Wednesday, September 28, 2005

Split a comma delimited string fast!

-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT
@intLoopCounter = @intLoopCounter +1
END
GO

--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO

--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO

--order the string
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO

--Get distinct values
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2'
SELECT DISTINCT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO

4 comments:

BTrenkAkaD said...

G'day just poped up from FR site to say... hello!

Cheers
BTaD

Razvan Socol said...

For other methods, including a comparison of their speed, see: http://www.sommarskog.se/arrays-in-sql.html

Anonymous said...

Smart code, better than my previous solution by using substring

Vadivel said...

Check this out ...
http://vadivel.blogspot.com/2006/05/split-function-in-sql-server-method-1.html