Split string into column

Question

Post reply

Split string into column

joemai

Hall of Fame

Points: 3496
More actions
July 7, 2005 at 11:34 am

#93505

Please someone rescue me. I have a string like this:

'action=acct_zip&id=1B25B81JVJT&eid=FD05063&custnbr=281050&ky=FD05063&oc=$99 Free Shipping&mwn=&zip=90650&email=lafaithhome@yahoo.com'

How can I use my select statement to split it up into 3 columns like this:

Custnbr Zip Email

281050 90650 lafaithhome@yahoo.com

My due date is today. Please urgently help!

Minh Vu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 728159

July 7, 2005 at 11:41 am

#571993

http://qa.sqlservercentral.com/columnists/sjones/tamestrings1.asp

http://qa.sqlservercentral.com/columnists/sjones/20010424135929_1.asp

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Ray M SSC-Insane Points: 21093 More actions · Answer 2

I set the string to a variable to help in the readability.

Declare @String varchar(1000)

set @String =

'action=acct_zip&id=1B25B81JVJT&eid=FD05063&custnbr=281050&ky=FD05063&oc=$99 Free Shipping&mwn=&zip=90650&email=lafaithhome@yahoo.com'

select substring(@String,(charindex('&custnbr=',@String) + 9), charindex('&', @String, (charindex('&custnbr=',@String) + 9)) - (charindex('&custnbr=',@String) + 9)) as custnbr,

substring(@String,(charindex('&zip=',@String) + 5), charindex('&', @String, (charindex('&zip=',@String) + 5)) - (charindex('&zip=',@String) + 5)) as zip

,substring(@String,(charindex('&email=',@String) + 7), 250) as Email

-- Results

custnbr,zip,Email

281050,90650,lafaithhome@yahoo.com

joemai Hall of Fame Points: 3496 More actions · Answer 3

joemai

Hall of Fame

Points: 3496

July 7, 2005 at 4:46 pm

#572120

Fantastic!

I really appeciate your help!

Minh Vu

Lowell SSC Guru Points: 323482 More actions · Answer 4

there's a bit of design issue here ;

the web page that this querystring was posted to had all those values in the Request.Querystring collection in the name-value combinations you wanted, but instead of grabbing the portions of the string and saving them, the whole string was saved to your db instead; you should tweak that page to put the values into the proper columns instead of a "catch all" field that holds the whole querystring.

your page should be grabbing Request.Querystring("CustNbr"), Request.Querystring("zip") and Request.Querystring("email") and saving it to your db;

here's a really simple way to see the name/value pairs:

For Each Key in Request.QueryString

Response.Write Key & ": " & Request.QueryString(Key) & "<BR>"

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!