Solved

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

Posted on 2004-08-31
8
500 Views
Last Modified: 2008-03-10
I have a column with the city, state and zipcode.  I need to split this column into three within a select statement.
0
Comment
Question by:lexo
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Assisted Solution

by:SjoerdVerweij
SjoerdVerweij earned 200 total points
ID: 11947900
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
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 50 total points
ID: 11947949
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
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 11947953
The code I posted will work if state is an abbrev or full and if your zips are 5 or 5-4.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11949984
If the above suggestions don't provide a working answer,
please let us know and post sample data
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:lexo
ID: 11953412
Why am I getting this error?

'varchar' is not a recognized CURSOR option.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11953982
Post your full query.
0
 

Author Comment

by:lexo
ID: 11954270
nevermind, I was missing an "@"
Thank you for your help!!
0
 

Author Comment

by:lexo
ID: 11954313
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 37
Backup Job question 4 20
SQL Server creating a temp table 7 39
Syntax using Declare 3 16
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now