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(',' &[list] & ',',[digit]+1,Instr([digit]+1,',' &[list] & ',',',')-([digit]+1)) AS [string]
FROM lists, numbers
WHERE digit=Instr(dig it,',' & 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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Lyle Fairfield]

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

Dr. StrangeLove <nospam@aol.com > wrote in news:403b6443$0 $195$75868355
@news.frii.net:
[color=blue]
> Modifying the query to handle any type of delimiter
> of any length is left as an exercise:)[/color]

uh huh!

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Dr. StrangeLove]

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

>> Modifying the query to handle any type of delimiter[color=blue][color=green]
>> of any length is left as an exercise:)[/color][/color]
[color=blue]
> uh huh![/color]

Yeah I get that alot.
Hint - look for the commas in quotes:)



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Jorgensen]

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

On 24 Feb 2004 15:29:00 GMT, Dr. StrangeLove <steve.nospam.@ rac4sql.net>
wrote:
[color=blue][color=green][color=darkred]
>>> Modifying the query to handle any type of delimiter
>>> of any length is left as an exercise:)[/color][/color]
>[color=green]
>> uh huh![/color]
>
>Yeah I get that alot.
>Hint - look for the commas in quotes:)
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]

I get it. You check every possible starting character position. Only trouble
is, you're creating 2 concatenated strings in the Where clause for every
possible starting character position for every row which is some serious heap
thrashing! It's probably more efficient, and definitely more legible code, to
simply loop trhough a recordset, and use a VBA function to split out arguments
and insert the rows into the other table.

[Dr. StrangeLove]

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

Steve,

I don't follow you.The where clause picks the same list value for the
number of delimited strings it has.It's all very simple:)
Perhaps this will help:

SELECT [id],list,digit,
Mid(',' &[list] & ',',[digit]+1,Instr([digit]+1,',' &[list] &
',',',')-([digit]+1)) AS [string]
FROM lists, numbers
WHERE digit=Instr(dig it,',' & list & ',',',') And digit<len(',' & list)
ORDER BY [id], [digit];

id list digit string
1 aa,bbb,c 1 aa
1 aa,bbb,c 4 bbb
1 aa,bbb,c 8 c
2 e,f,gggg,hh 1 e
2 e,f,gggg,hh 3 f
2 e,f,gggg,hh 5 gggg
2 e,f,gggg,hh 10 hh
3 ii,kk 1 ii
3 ii,kk 4 kk
4 m 1 m
5 nn,pp 1 nn
5 nn,pp 4 pp
6 q,RRRRRRR,s 1 q
6 q,RRRRRRR,s 3 RRRRRRR
6 q,RRRRRRR,s 11 s

P.S. As I recall you still don't believe me when
I tell you that the same value can be Updated
multiple times:)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Jorgensen]

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

On 24 Feb 2004 23:18:35 GMT, Dr. StrangeLove <steve.nospam.@ rac4sql.net>
wrote:
[color=blue]
>Steve,
>
>I don't follow you.The where clause picks the same list value for the
>number of delimited strings it has.It's all very simple:)
>Perhaps this will help:
>
>SELECT [id],list,digit,
> Mid(',' &[list] & ',',[digit]+1,Instr([digit]+1,',' &[list] &
>',',',')-([digit]+1)) AS [string]
>FROM lists, numbers
>WHERE digit=Instr(dig it,',' & list & ',',',') And digit<len(',' & list)
>ORDER BY [id], [digit];
>
>id list digit string
>1 aa,bbb,c 1 aa
>1 aa,bbb,c 4 bbb
>1 aa,bbb,c 8 c
>2 e,f,gggg,hh 1 e
>2 e,f,gggg,hh 3 f
>2 e,f,gggg,hh 5 gggg
>2 e,f,gggg,hh 10 hh
>3 ii,kk 1 ii
>3 ii,kk 4 kk
>4 m 1 m
>5 nn,pp 1 nn
>5 nn,pp 4 pp
>6 q,RRRRRRR,s 1 q
>6 q,RRRRRRR,s 3 RRRRRRR
>6 q,RRRRRRR,s 11 s[/color]

Oh, I understand what it's doing, and why it works, but internally, it's
processing every row of the source table for every row of the numbers table,
and for each of those combinations, it's building 2 temporary strings on the
heap even though only a small percentage of them will turn out to be used to
generate output (those that match the beginning of a new argument). That's an
awful lot of heap thrashing. Furthermore, just because code is technically
right, doesn't mean one ought to use it if it also happens to be really
obtuse.

What if someone needed to modify that code to, for instance, recognize either
',' or '|' as a delimiter? How much time would they spend untangling the
meaning of what's there first, how much time deciding whether the query could
be modified to handle that case, and how much time reimplementing the code
using the recordset loop scheme if they failed (or simply decided it wasn't
worth the cost) to modify the existing code? At how much cost to the client?

Clever is fun, but it's not always a good choice.
[color=blue]
>
>P.S. As I recall you still don't believe me when
>I tell you that the same value can be Updated
>multiple times:)[/color]

Did I say that? If so, I do acknowledge that I was wrong. It is true,
though, that if you update a value multiple times in a query, and the updates
could set different values, you won't know which update will take. Also, one
update won't "see" the data written by another update from within the same
query - usually, that's a good thing, but it's good to know.

[Dr. StrangeLove]

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

Steve Jorgensen write:[color=blue]
>What if someone needed to modify that code to, for instance, recognize[/color]
either ',' or '|' as a delimiter? How much time would they spend
untangling the meaning of what's there first, how much time deciding
whether the query could
be modified to handle that case, and how much time implementing the code
using the recordset loop scheme if they failed (or simply decided it
wasn't worth the cost) to modify the existing code? At how much[color=blue]
>cost to the client?[/color]

Want multiple delimiters?You don't have to write any
code.I put it in RAC:).

Check out:

http://www.rac4sql.net/onlinehelp.asp

Go to:
Working with Character Strings
Splitting Strings
3. Using Multiple Delimiters to Split Delimited Strings

Be my guest to give it a go:)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Jorgensen]

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

On 25 Feb 2004 00:18:26 GMT, Dr. StrangeLove <steve.nospam.@ rac4sql.net>
wrote:
[color=blue]
>Steve Jorgensen write:[color=green]
>>What if someone needed to modify that code to, for instance, recognize[/color]
>either ',' or '|' as a delimiter? How much time would they spend
>untangling the meaning of what's there first, how much time deciding
>whether the query could
>be modified to handle that case, and how much time implementing the code
>using the recordset loop scheme if they failed (or simply decided it
>wasn't worth the cost) to modify the existing code? At how much[color=green]
>>cost to the client?[/color]
>
>Want multiple delimiters?You don't have to write any
>code.I put it in RAC:).
>
>Check out:
>http://www.rac4sql.net/onlinehelp.asp
>Go to:
>Working with Character Strings
> Splitting Strings
>3. Using Multiple Delimiters to Split Delimited Strings
>
>Be my guest to give it a go:)
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]

I think you're missing my point. Just because a thing is possible to do a
certain way doesn't mean it's good to do it that way. If I invent a cool new
way of doing accounting, then hand my work to my accountant, I just really did
a whopper on my account balance after I pay the accountant for the extra time
to figure out what I did. Also, don't forget what my friend Sam Gray says -
"You + time = somebody else".

[Steve Jorgensen]

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

On Wed, 25 Feb 2004 00:57:22 GMT, Chuck Grimsby
<c.grimsby@worl dnet.att.net.in valid> wrote:

....[color=blue]
>In VBA, which Access uses in place of complex stored procedures, the
>task is even more trivial. Not as fast perhaps, but easier to write.
>[/color]

In this particular case, probably faster, actually, but it would depend how
many rows, and how wide the fields are.

[Steve Jorgensen]

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

On Wed, 25 Feb 2004 10:46:13 GMT, Chuck Grimsby
<c.grimsby@worl dnet.att.net.in valid> wrote:
[color=blue]
>On Wed, 25 Feb 2004 01:04:35 GMT, Steve Jorgensen
><nospam@nospam .nospam> wrote:
>[color=green]
>>On Wed, 25 Feb 2004 00:57:22 GMT, Chuck Grimsby
>><c.grimsby@wo rldnet.att.net. invalid> wrote:[color=darkred]
>>>In VBA, which Access uses in place of complex stored procedures, the
>>>task is even more trivial. Not as fast perhaps, but easier to write.[/color][/color]
>[color=green]
>>In this particular case, probably faster, actually, but it would depend how
>>many rows, and how wide the fields are.[/color]
>
>Perhaps. The problem in Access is that all the records still have to
>"travel down the wire" so that the VBA function can process the
>records on the local computer.[/color]

The data would still have to do that with the query. Of course, if this were
all done using a server back-end, and translating the VBA function calls to
native server function calls, the equation would be different.