Solved

parse city state using Tsql

Posted on 2014-01-17
5
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

752 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