Solved

parse city state using Tsql

Posted on 2014-01-17
5
347 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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