mishaluba (6/27/2010)
Thank you very much, lmu92 ! Great solution and exactly what I need.As a side note I did a search on this site for "DelimitedSplit8K", but I can't seem to find it. However I do understand that the one I listed above is inefficient. Below is the alternative, is it similar to yours?
CREATE FUNCTION [dbo].[fnSplit]
(@list VARCHAR(8000),
@delim CHAR(1) = ','
) RETURNS TABLE AS
RETURN
WITH csvtbl(START, stop) AS (
SELECT START = 1,
stop = CHARINDEX(@delim, @list + @delim)
UNION ALL
SELECT START = stop + 1,
stop = CHARINDEX(@delim,
@list + @delim, stop + 1)
FROM csvtbl
WHERE stop > 0
)
SELECT row_number() over (order by Start) as ItemID,
LTRIM(RTRIM(SUBSTRING(@list, START, CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
AS ItemValue
FROM csvtbl
WHERE stop > 0
GO
mishaluba (6/27/2010)
Thank you very much, lmu92 ! Great solution and exactly what I need.As a side note I did a search on this site for "DelimitedSplit8K", but I can't seem to find it. However I do understand that the one I listed above is inefficient. Below is the alternative, is it similar to yours?
CREATE FUNCTION [dbo].[fnSplit]
(@list VARCHAR(8000),
@delim CHAR(1) = ','
) RETURNS TABLE AS
RETURN
WITH csvtbl(START, stop) AS (
SELECT START = 1,
stop = CHARINDEX(@delim, @list + @delim)
UNION ALL
SELECT START = stop + 1,
stop = CHARINDEX(@delim,
@list + @delim, stop + 1)
FROM csvtbl
WHERE stop > 0
)
SELECT row_number() over (order by Start) as ItemID,
LTRIM(RTRIM(SUBSTRING(@list, START, CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
AS ItemValue
FROM csvtbl
WHERE stop > 0
GO
That's a recursive CTE and can be as bad as a While Loop for CPU and will usually be about 3 times worse on the number of reads. It's just another form of RBAR and it should usually be avoided.
Yes... you are correct... the search capabilities on this forum suck. I'm sure that Lutz will be back soon with his split function.
--Jeff Moden
Change is inevitable... Change for the better is not.