Split City, State ZIP into three columns from one in SELECT statement

I have a column with the city, state and zipcode.  I need to split this column into three within a select statement.
lexoAsked:
Who is Participating?
 
ChrisFretwellConnect With a Mentor Commented:
As long as its formatted correctly (consistantly),

declare  @temp1 varchar(255)

set @temp1 = 'city, state 99999-5555'
--set @temp1 = 'city, state 99999'

select left(@temp1,charindex(',',@temp1)-1) as city,
         substring(@temp1, charindex(',',@temp1) + 2,charindex(' ',@temp1,charindex(',',@temp1))) as state,
         substring(@temp1,charindex(' ',@temp1,charindex(',',@temp1))+ charindex(',',@temp1)+2,len(@temp1)-(charindex(' ',@temp1,charindex(',',@temp1))+ charindex(',',@temp1))) as zip
0
 
SjoerdVerweijConnect With a Mentor Commented:
declare @t varchar(50)

set @t = 'Sacramento, CA 95999'

select
  city = left(@t, charindex(',', @t) - 1),
  state = rtrim(ltrim(substring(@t, charindex(',', @t) + 1, 3))),
  zip = right(@t, 5)

Replace @t with your column name to use the expressions in a query.
0
 
ChrisFretwellCommented:
The code I posted will work if state is an abbrev or full and if your zips are 5 or 5-4.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HilaireCommented:
If the above suggestions don't provide a working answer,
please let us know and post sample data
0
 
lexoAuthor Commented:
Why am I getting this error?

'varchar' is not a recognized CURSOR option.
0
 
SjoerdVerweijCommented:
Post your full query.
0
 
lexoAuthor Commented:
nevermind, I was missing an "@"
Thank you for your help!!
0
 
lexoAuthor Commented:
I split the Points, giving SjoerdVerweij the majority because he posted first, and the code works well.  However, ChrisFretwell's code also works, and it has additional functionality.
Again, thank you all!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.