Solved

parse city state using Tsql

Posted on 2014-01-17
5
348 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
ID: 39789756
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
ID: 39789771
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
ID: 39789776
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
ID: 39789788
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
ID: 39789808
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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