Credits:
Author: Unknown
Date: 10 Apr 2007
Description
While not strictly data conversion, this piece of code is one that wears many hats (Conversion, Manipulation, Pivoting, Transformation) and this was the first of these categories that I uploaded to the site, so this is where the code ended up.
Several times I have looked at this code with the intent of figuring out just how it works. Time then runs on and I end up like this little guy. You'll see what I mean when you look at it yourself.
I just know that it works reliably, and very fast for 99% of the strings that I've needed to split. There is always the occasional odd one - e.g. a multi-character delimiter - that it will not cope with.
Code
|
DROP FUNCTION dbo.udf_split
GO
CREATE FUNCTION dbo.udf_split
(
@List VARCHAR(8000),
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
(
Value VARCHAR(8000)
)
AS
BEGIN
SET @List = REPLACE(REPLACE(@List,CHAR(10),''),CHAR(13),'')
INSERT @Results
SELECT LTRIM(SUBSTRING(@Delimiter + @List + @Delimiter , w.i + 1, CHARINDEX(@Delimiter , @Delimiter + @List + @Delimiter , w.i + 1) - w.i - 1)) value
FROM
(
SELECT TOP 100 PERCENT
v0.n + v1.n + v2.n + v3.n i
FROM
(
SELECT 0 n UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION
SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15
) AS v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL
SELECT 32 UNION ALL SELECT 48 UNION ALL
SELECT 64 UNION ALL SELECT 80 UNION ALL
SELECT 96 UNION ALL SELECT 112 UNION
SELECT 128 UNION ALL SELECT 144 UNION ALL
SELECT 160 UNION ALL SELECT 176 UNION ALL
SELECT 192 UNION ALL SELECT 208 UNION ALL
SELECT 224 UNION ALL SELECT 240
) AS v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL
SELECT 512 UNION ALL SELECT 768 UNION ALL
SELECT 1024 UNION ALL SELECT 1280 UNION ALL
SELECT 1536 UNION ALL SELECT 1792 UNION
SELECT 2048 UNION ALL SELECT 2304 UNION ALL
SELECT 2560 UNION ALL SELECT 2816 UNION ALL
SELECT 3072 UNION ALL SELECT 3328 UNION ALL
SELECT 3584 UNION ALL SELECT 3840
) AS v2,
(
SELECT 0 n UNION ALL SELECT 4096
) AS v3 ORDER BY i
) w
WHERE w.i = CHARINDEX(@Delimiter , @Delimiter + @List + @Delimiter , w.i)
AND w.i < LEN(@Delimiter + @List)
RETURN
END
/*
DECLARE @ListVar VARCHAR (100)
SELECT @ListVar = 'this, is, a, comma,delimited, list'
SELECT * FROM dbo.udf_split(@ListVar, ',')
*/
|
 Updating... Andy Hughes, Jun 7, 2012, 10:54 AM
|
