CSZ field needs to be separated

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)

CSZ field needs to be separated

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-03-28 : 13:41:18

hi all,

I have field CSZ(city, state zip) like (Boston, PA 85985)

how can i make it separate in three column for C, S, Zip!!

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 16:58:00

see here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58215&whichpage=2

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-04-11 : 11:07:51

is there a other way of doing this please!

thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-11 : 11:30:46

Hi Pat,

Can't you just use some standard string manipulation functions? I'm not that familiar with the csz format, but something like this should get you started...

--data
declare @t table (csz varchar(60))
insert @t (csz) values ('Boston, PA 85985')

--calculation
select
    left(csz, charindex(',', csz)-1) as city,
    left(right(csz, charindex(' ', reverse(csz))+2), 2) as state,
    right(csz, charindex(' ', reverse(csz))-1) as zip
from @t

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources