473,387 Members | 1,528 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

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

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(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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
9 14678
Dr. StrangeLove <no****@aol.com> wrote in news:403b6443$0$195$75868355
@news.frii.net:
Modifying the query to handle any type of delimiter
of any length is left as an exercise:)


uh huh!

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #2
>> Modifying the query to handle any type of delimiter
of any length is left as an exercise:)
uh huh!


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!
Nov 12 '05 #3
On 24 Feb 2004 15:29:00 GMT, Dr. StrangeLove <steve.nospam.@rac4sql.net>
wrote:
Modifying the query to handle any type of delimiter
of any length is left as an exercise:)

uh huh!


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!


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.
Nov 12 '05 #4
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(digit,',' & 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!
Nov 12 '05 #5
On 24 Feb 2004 23:18:35 GMT, Dr. StrangeLove <steve.nospam.@rac4sql.net>
wrote:
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(digit,',' & 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
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.

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


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.
Nov 12 '05 #6
Steve Jorgensen write:
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 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 muchcost to the client?


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!
Nov 12 '05 #7
On 25 Feb 2004 00:18:26 GMT, Dr. StrangeLove <steve.nospam.@rac4sql.net>
wrote:
Steve Jorgensen write:
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 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
cost to the client?


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!


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".
Nov 12 '05 #8
On Wed, 25 Feb 2004 00:57:22 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

....
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.


In this particular case, probably faster, actually, but it would depend how
many rows, and how wide the fields are.
Nov 12 '05 #9
On Wed, 25 Feb 2004 10:46:13 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:
On Wed, 25 Feb 2004 01:04:35 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:
On Wed, 25 Feb 2004 00:57:22 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:
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.

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


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.


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.
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Mark Harrison | last post by:
What is the best way to process a text file of delimited strings? I've got a file where strings are quoted with at-signs, @like this@. At-signs in the string are represented as doubled @@. ...
6
by: Rudolf Bargholz | last post by:
Hi , I have the following tables ------------- PAX: Id Order_Id Name Position
20
by: Opettaja | last post by:
I am new to c# and I am currently trying to make a program to retrieve Battlefield 2 game stats from the gamespy servers. I have got it so I can retrieve the data but I do not know how to cut up...
4
by: Kurt | last post by:
I'm using the fConcatChild function posted at http://www.mvps.org/access/modules/mdl0004.htm to return a field from the Many table of a 1:M relationship into a concatenated string. The function...
25
by: electrixnow | last post by:
in MS VC++ Express I need to know how to get from one comma delimited text string to many strings. from this: main_string = "onE,Two,Three , fouR,five, six " to these: string1 =...
4
by: Jazzer | last post by:
I want to 'flatten' two tables into one by combining the '1-n' values from the 'child' records into a single concatenated string within the parent by using queries. I.E. create a single NVarChar...
3
bilibytes
by: bilibytes | last post by:
Hi, I am having a problem with a concatenated string. I start my string outside of a for() and then, concatenate the string generated with the loop to it. the string out of the loop looks like...
1
by: jremio | last post by:
Hey I was wondering how do I go about doing this. strCommand = readCommand("aaa;sss;ddd", 1) MsgBox (strCommand) Public Function readCommand(str As String, position As Integer) As String...
4
by: fmuddy | last post by:
hi everybody, I am having problem with "|" character while splitting text or string. but all other characters are working like (/ ; , - _). Does "|" has a special property or does it related...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.