Pull information out of feild with commas

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

General SQL Server Forums

New to SQL Server Programming

Pull information out of feild with commas

Author

Topic

Matt23
Starting Member

5 Posts

Posted - 2010-07-15 : 10:42:28

I have a field that stores the city ,state,zipcode all in one field. I want to be able to pull these all out separately. For example

Chicago, Illinois, 69144

I want to pull it out so there is a City field, State Field, and Zip Field.

I am fairly new to SQL so any help would be great.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 10:51:18

select
parsename(replace('Chicago, Illinois, 69144',',','.'),1) as [zip],
parsename(replace('Chicago, Illinois, 69144',',','.'),2) as [state],
parsename(replace('Chicago, Illinois, 69144',',','.'),3) as [city]

and instead of 'Chicago, Illinois, 69144' you can use the columnname which holds the data to be splitted.

No, you're never too old to Yak'n'Roll if you're too young to die.

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-15 : 11:03:11

That's a pretty slick solution, webfred. I had to google parsename to see exactly what you were doing there.

Very nice.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 11:06:08

Yes - it is a "lazy people" solution but it should work if the OP isn't coming up with:
Ah, sometimes the data looks a bit different ...

No, you're never too old to Yak'n'Roll if you're too young to die.

Matt23
Starting Member

5 Posts

Posted - 2010-07-15 : 11:57:09

I don't want to change the data just pull the data out in chunks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 12:08:26

select
parsename(replace(Your_Column,',','.'),1) as [zip],
parsename(replace(Your_Column,',','.'),2) as [state],
parsename(replace(Your_Column,',','.'),3) as [city],
... some other columns ...
from Your_Table

No, you're never too old to Yak'n'Roll if you're too young to die.

Matt23
Starting Member

5 Posts

Posted - 2010-07-15 : 14:15:19

Works great thanks a lot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 14:17:37

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources