Solved

parse city state using Tsql

Posted on 2014-01-17
5
335 Views
Last Modified: 2014-01-17
Good Afternoon,

I have a column that contains city state like this

P-PAYEE-ADDR-3
CHANDLER AZ
CORPUS CHRISTI TX
MAPLEWOOD NJ
WOODLAND HILLS CA
WOODLAND HILLS CA
PHILADELPHIA PA
WOODLAND HILLS CA
PHILADELPHIA PA
RICHARDSON TX
SAN ANTONIO TX

I need to parse city and state. The issue I run into is that in many cases the city name is two words with a space between the words.  

When I use this code:
[P-CITY] = substring([P-PAYEE-ADDR-3],1,CHARINDEX(' ', [P-PAYEE-ADDR-3]))

I get these results.

P-CITY                    P-STATE
CHANDLER              AZ
CORPUS                       TX
MAPLEWOOD       NJ
WOODLAND       CA
WOODLAND       CA
PHILADELPHIA       PA
WOODLAND       CA
PHILADELPHIA       PA
RICHARDSON       TX
SAN                       TX
GAINESVILLE       GA

Can you help me please?

Thank you!
0
Comment
Question by:Leogal
5 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 167 total points
Comment Utility
ok, try this

select left([P-PAYEE-ADDR-3],len([P-PAYEE-ADDR-3])-2) as [p-city], RIGHT([P-PAYEE-ADDR-3],2) as [P-STATE]

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
Comment Utility
Correct.  If the last two characters is the state code, then all you can do is assume everything left of that is the city.
0
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 166 total points
Comment Utility
If you always have the State in the same format at the end of the field, you could do something like this:

SELECT LEFT(P-PAYEE-ADDR-3, LEN(P-PAYEE-ADDR-3)-3) AS [P-CITY],
RIGHT(P-PAYEE-ADDR-3,2) AS [P-STATE]

Open in new window



Edit: Sorry, posted too late.
0
 

Author Comment

by:Leogal
Comment Utility
with a small modifiction

select   left([P-PAYEE-ADDR-3],len([P-PAYEE-ADDR-3])-2) as [p-city], RIGHT([P-PAYEE-ADDR-3],3) as [P-STATE]

 it returns the two character state like this



p-city                      P-STATE
CHANDLER       AZ
CORPUS CHRISTI       TX
MAPLEWOOD       NJ
WOODLAND HILLS       CA
WOODLAND HILLS       CA
PHILADELPHIA       PA
WOODLAND HILLS       CA
PHILADELPHIA       PA
RICHARDSON       TX
SAN ANTONIO       TX
GAINESVILLE       GA

and returns this message  
Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
The first comment provided works in my SSMS.

>Invalid length parameter passed to the SUBSTRING function.
You may have a value which is not NULL and less than 3 characters, which means the RIGHT function throw an error.

CREATE TABLE #addr (cs varchar(30))

INSERT INTO #addr (cs)
VALUES 
	('CHANDLER       AZ'), 
	('CORPUS CHRISTI       TX'),
	('MAPLEWOOD       NJ') ,
	('WOODLAND HILLS       CA'),
	('WOODLAND HILLS       CA'),
	('PHILADELPHIA       PA'),
	('WOODLAND HILLS       CA'), 
	('PHILADELPHIA       PA'), 
	('RICHARDSON       TX') ,
	('SAN ANTONIO       TX'), 
	('GAINESVILLE       GA') 

select   left(cs,len(cs)-2) as [p-city], RIGHT(cs,3) as [P-STATE] FROM #addr

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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