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:

select VALUE from PARSE_STRING(‘a,b,c,d,e’, ‘,’)

GO

Real World Usage:

select

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

GO

 
The Function:
 

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

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

— Author: Joel Holder

— Description: parses a delimeted string and returns a table

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

 

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

IF

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’))

    DROP

FUNCTION [dbo].[PARSE_STRING]

go

CREATE

FUNCTION PARSE_STRING  (@string nvarchar(max),  @delimeter char(1))

RETURNS

@result_table TABLE (POSITION int, VALUE nvarchar(max))

AS

BEGIN

— Fill the table variable with the rows for your result set

declare @pos int

declare @piece varchar(500)

declare @id int

set @id = 0

— Need to tack a delimiter onto the end of the input string if one doesn’t exist

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

    set @string = @string + @delimeter

    set @pos = patindex(‘%’ + @delimeter + ‘%’ , @string)

    while (@pos <> 0)

        begin

            set @id = @id + 1

            set @piece = left(@string, @pos 1)

            — You have a piece of data, so insert it, print it, do whatever you want to with it.

            insert into @result_table values (@id, @piece)

            set @string = stuff(@string, 1, @pos, )

            set @pos = patindex(‘%’ + @delimeter + ‘%’ , @string)

        end

 

return

END 

GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s