Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Split A String By Using A Number Table

From Wiki

Jump to: navigation, search

Using a number table is a very fast way to split a string. You can even use it inside a function if you want to. If You don't have a number table already then create this table now. Remeber this is a one time deal.

  1. -- Create our Pivot table ** do this only once-- populate it with 1000 rows
  2.     CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
  3.     DECLARE @intLoopCounter INT
  4.     SELECT @intLoopCounter =0
  5.     SET NOCOUNT ON
  6.      
  7.     WHILE @intLoopCounter <=999 BEGIN
  8.        INSERT INTO NumberPivot
  9.        VALUES (@intLoopCounter)
  10.        SELECT @intLoopCounter = @intLoopCounter +1
  11.     END
  12.     GO


Here are the queries

Numbers

  1. DECLARE @chvGroupNumbers VARCHAR(1000)
  2.     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,'
  3.      
  4.     SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
  5.     CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS VALUE
  6.     FROM NumberPivot
  7.     WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
  8.     AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
  9.     GO


Characters

  1. DECLARE @chvGroupNumbers VARCHAR(1000)
  2.     SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'
  3.      
  4.     SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
  5.     CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS VALUE
  6.     FROM NumberPivot
  7.     WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
  8.     AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
  9.     GO


Order the string

  1. DECLARE @chvGroupNumbers VARCHAR(1000)
  2.     SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'
  3.      
  4.     SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
  5.     CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS VALUE
  6.     FROM NumberPivot
  7.     WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
  8.     AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
  9.     ORDER BY 1
  10.     GO


Get distinct values

  1. DECLARE @chvGroupNumbers VARCHAR(1000)
  2.     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'
  3.      
  4.     SELECT DISTINCT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
  5.     CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS VALUE
  6.     FROM NumberPivot
  7.     WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
  8.     AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
  9.     ORDER BY 1
  10.     GO


Contributed by: --SQLDenis 02:27, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Transposing and pivoting

131 Rating: 2.3/5 (3 votes cast)