FYI - Sql for splitting a delimited concatenated string into separate strings/rows.

[Dr. StrangeLove]

Greetings,

Let say we want to split column 'list' in table lists
into separate rows using the comma as the delimiter.

Table lists
id list
1 aa,bbb,c
2 e,f,gggg,hh
3 ii,kk
4 m
5 nn,pp
6 q,RRRRRRR,s

First we need a table of consecutive integers from 1 to say 100.
Table numbers has a single column 'digit'.The largest digit
should be >= the length of the largest string to split (list).
digit
1
2
3
..
100

Now we can use this query:

SELECT [id], Mid(',' &

• & ',',[digit]+1,Instr([digit]+1,',' &
  • &
    ',',',')-([digit]+1)) AS [string]
    FROM lists, numbers
    WHERE digit=Instr(digit,',' & list & ',',',') And digit<len(',' & list)
    ORDER BY [id],[digit];
    
    id string
    1 aa
    1 bbb
    1 c
    2 e
    2 f
    2 gggg
    2 hh
    3 ii
    3 kk
    4 m
    5 nn
    5 pp
    6 q
    6 RRRRRRR
    6 s
    
    Modifying the query to handle any type of delimiter
    of any length is left as an exercise
    
    You don't need to write functions for many operations
    (such as forming concatenated strings from rows) that you have
    been told you need!.
    
    For crosstabs and much more in Sql Server check out RAC.
    Free query tool for any Sql Server version - QALite.
    Check out www.rac4sql.net

 

[Michel Walsh]

Hi,


Quite interesting. With a light modification, but using the same basic
idea, we can avoid some of the arithmetic and the Volkswagen looks (
',',',' ) :


SELECT lists.id,
Mid( list & ',' , digit) As temp,
Left(temp, InStr( temp , ',' ) -1) AS element
FROM lists, numbers
WHERE ',' = Mid(',' & list, digit, 1)
ORDER BY id, digit



sure, "temp" is not useful, in itself, someone can cut and paste it in the
expression for element:


SELECT lists.id,
Left( Mid(list & ',' ,digit), InStr( Mid(list & ',' , digit),
',' ) -1) AS element
FROM lists, numbers
WHERE ',' = Mid(',' & list, digit, 1)
ORDER BY id, digit



It also handles the cases where list is NULL ( as does your statement).

With Jet, it seems the optimizer gave up, and the query run time is
proportional to the number of rows in table numbers...

 

[Dr. StrangeLove]

Hi,


Quite interesting. With a light modification, but using the same basic
idea, we can avoid some of the arithmetic and the Volkswagen looks (
',',',' ) :


SELECT lists.id,
Mid( list & ',' , digit) As temp,
Left(temp, InStr( temp , ',' ) -1) AS element
FROM lists, numbers
WHERE ',' = Mid(',' & list, digit, 1)
ORDER BY id, digit



sure, "temp" is not useful, in itself, someone can cut and paste it in the
expression for element:


SELECT lists.id,
Left( Mid(list & ',' ,digit), InStr( Mid(list & ',' , digit),
',' ) -1) AS element
FROM lists, numbers
WHERE ',' = Mid(',' & list, digit, 1)
ORDER BY id, digit



It also handles the cases where list is NULL ( as does your statement).

With Jet, it seems the optimizer gave up, and the query run time is
proportional to the number of rows in table numbers...

Very kewl.
I'll have to try this in MySql