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:

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.

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