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 ONGO
SET
QUOTED_IDENTIFIER ONGO
— =============================================
— 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) endreturn
END
GO
Joel Holder's Code Compendium 