asked on

parse city state using Tsql

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!

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Simone B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leogal

ASKER

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.

Jim Horn

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