Parsing Strings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parsing Strings

Author

Topic

Hannibal
Starting Member

20 Posts

Posted - 2003-02-10 : 15:44:38

I retrieve a column from the database and want to merge multiple instances/rows into one column in one row and at the same time, parse some information. The first part (merging) is possible using coalesce, but I'm not sure how to parse it at the same time.

The data from my colum looks like:

card_data=3,9,14,5,10,24,17,22,29,23,34,37,38,41,59,50,47,52,46,66,67,69,73,71&card_id=771

Which will look like this after I merge the multiple rows:

card_data=3,9,14,5,10,24,17,22,29,23,34,37,38,41,59,50,47,52,46,66,67,69,73,71&card_id=771?card_data=3,9,14,5,10,24,17,22,29,23,34,37,38,41,59,50,47,52,46,66,67,69,73,71&card_id=772

My goal is to remove everything except the card_id numbers, ie:

771, 772, ###

I've read some of the articles and searched the forums, but I can't find anything that resembles a SPLIT function that would enable me to do this in a single select statement. Any suggestions?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-10 : 15:49:45

Try this article: [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp[/url].

Hannibal
Starting Member

20 Posts

Posted - 2003-02-10 : 16:17:31

Thanks for the article, although I'm not sure if it would accomplish what I'm looking for (doing it in a single select statement is ideal).

Anyway, after playing around for a little while, I came up with this:


declare @card_id_list varchar(4096)

select @card_id_list = coalesce(@card_id_list + ',','') + substring(cast(substring(event.description,charindex('&',event.description)-1,len(event.description)) as varchar),charindex('=',event.description)+1,len(event.description))
from event

select @card_id_list

Which produces: 771,772

So, I have a solution, but is it efficient as possible?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-10 : 17:12:22

As far as performance goes, I couldn't tell you without trying it and looking at the performance, query plan, etc. I try to avoid code like that like the plague, lol.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources