TSQL function to parse delimeted string and return a memory table containing the values

This script is useful when you need to generate a rowset from a delimeted string.  It allows the “WHERE IN” clause to be used against the values in the string, because they are converted into table rows.  This UDF is designed to be plugged into other queries where this need is present.  Here’s an example of how to use it:

 

Basic Usage:

</p>

select</font> VALUE from PARSE_STRING(‘a,b,c,d,e’, ‘,’)

GO

Real World Usage:

</p>

select

</font> * from customers where last_name in (select VALUE from PARSE_STRING(‘obama,biden,clinton,holder’, ‘,’))

GO

</p> </div>

 

The Function:

 

</p>

SET

</font> ANSI_NULLS ON </p>

GO

</font> </p>

SET

</font> QUOTED_IDENTIFIER ON </p>

GO

</font> </p>

— =============================================

— Author: Joel Holder

— Description: parses a delimeted string and returns a table

— =============================================

 

/****** Object: UserDefinedFunction [dbo].[PARSE_STRING] ******/

</p> </p>

</font> </p>

IF

</font> EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[PARSE_STRING]’) AND type in (N‘FN’, N‘IF’, N‘TF’, N‘FS’, N‘FT’)) </p>

    DROP

</font> FUNCTION [dbo].[PARSE_STRING] </p>

go

</font> </p>

CREATE

</font> FUNCTION PARSE_STRING  (@string nvarchar(max),  @delimeter char(1)) </p>

RETURNS

</font> @result_table TABLE (POSITION int, VALUE nvarchar(max)) </p>

AS

BEGIN

</p>

</font> </p>

</font>— Fill the table variable with the rows for your result set </p>

</font>declare @pos int </p>

</font>declare @piece varchar(500) </p>

</font>declare @id int </p>

</font>set @id = 0 </p>

</font>— Need to tack a delimiter onto the end of the input string if one doesn’t exist </p>

</font>    if right(rtrim(@string),1) <> @delimeter </p>

</font>    set @string = @string + @delimeter </p>

</font>    set @pos = patindex(‘%’ + @delimeter + ‘%’ , @string) </p>

</font>    while (@pos <> 0) </p>

</font>        begin </p>

</font>            set @id = @id + 1 </p>

</font>            set @piece = left(@string, @pos – 1) </p>

</font>            — You have a piece of data, so insert it, print it, do whatever you want to with it. </p>

</font>            insert into @result_table values (@id, @piece) </p>

</font>            set @string = stuff(@string, 1, @pos, ”) </p>

</font>            set @pos = patindex(‘%’ + @delimeter + ‘%’ , @string) </p>

</font>        end </p>

 

</font>return </p>

END</font> </p>

GO

</font></div> </div>