String Parsing in TSQL

2014-10-06
Bob Blackburn

SQL is designed for set based operations. It does include some ability for row based programming and string manipulation.  It may not be an optimal solution. But, if it is a small task or using a different language or tool is not an option, it can be done. Today we will create a function to break out a list from a string input.

We will use a familiar form, the doctor’s patient background form. Here we have to list allergies and surgeries. Our job is to get this information in the database. We want to convert them from string lists into rows for processing.

First, let’s create the table and load some data.

Select Cast(1 as int) as PatientID, cast('Allergies' as varchar(30)) as CategoryHeading, cast('NKDA' as varchar(200)) as CategoryList
  into PatientUpload

INSERT INTO [dbo].[PatientUpload]
           ([PatientID]
           ,[CategoryHeading]
           ,[CategoryList])
     VALUES
           (1, 'Surgeries', 'Wisdom teeth, appendectomy')
          ,(2, 'Allergies', 'Iodine, latex, morphine')
          ,(2, 'Surgeries', 'Pace maker, hip replacement')
GO

We get an input table of:

PatientID
CategoryHeading
CategoryList
1
Allergies
NKDA
1
Surgeries
Wisdom teeth, appendectomy
2
Allergies
Iodine, latex, morphine
2
Surgeries
Pace maker, hip replacement

Next we will create a function to parse the string. It will accept the string and the delimiter. The function will process the string and return a table with the values between the delimiter.

Create Function ufnGetList(@List varchar(200), @Delim char(1))
Returns @Category Table
(ListItem varchar(200))
as

Begin
       Declare @x int = 1
              ,@str varchar(201) = @List + @Delim;

       While @x < LEN(rtrim(@List))
       begin
      
       insert into @Category
          values (ltrim(rtrim(substring(@str, @x, charindex(@Delim, @str, @x) - @x))));
       set @x = (charindex(@Delim, @str, @x) + 1);
       end; -- While

       Return;

End; --Function ufnGetList
go

To return the input in row form, we will use a CROSS APPLY in the Select statement. CROSS APPLY is for SQL Server. Different databases will require another solution.

SELECT PatientID
      ,CategoryHeading
      ,ListItem                  
  FROM dbo.PatientUpload
  cross apply dbo.ufnGetList(CategoryList, ',')

Now our input can be processed in table form.

PatientID
CategoryHeading
ListItem
1
Allergies
NKDA
1
Surgeries
Wisdom teeth
1
Surgeries
appendectomy
2
Allergies
Iodine
2
Allergies
latex
2
Allergies
morphine
2
Surgeries
Pace maker
2
Surgeries
hip replacement


If you are new to string processing in TSQL, I hope this will give you an idea of some of the capabilities.