SQL User Defined Function to Parse a Delimited String
Below
function takes as parameter a text, split the text in tokens, and returns a
table containing all this tokens.
CREATE FUNCTION ParseText2File(@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while
len(@p_text) > 0 and charindex(@p_delimiter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_delimiter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_delimiter,@p_text))
end
insert @tempResults select @p_text
insert @results
select result
from @tempResults
return
END
Let’s see a practical case when this function can be used. You have a list
of products. Each product belongs to a specific category. You need to return
all products belonging to a list of categories.
An approach could be to create a stored procedure GetProducts(@categories
varchar(4000)) which has a parameter containing all categories ids separated by
comma like this: '1,3,6,11'. Using the above function,
the solution of our problem is simple:
CREATE PROCEDURE
GetProducts(@categories varchar(4000))
As
SELECT p.productId, p.productName
FROM products p
WHERE p.categoryId IN (SELECT id
FROM dbo.ParseText2Table(@categories))
A different approach can be found here:
http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
P.S. Of course, you can take in consideration sp_executesql function.