remove state from city/state column

Question

remove state from city/state column

taosun

SSC-Addicted

Points: 414
More actions
June 10, 2009 at 10:21 pm

#205362

I have some name and address data that has the city and state in one column. I have created a state column and moved the states into that column. Now I can't figure out how to get the State part of City/State out of that column so I just have the city in it. Typical data is like Chicago Il or New York Ny.
Thanks,
Bill

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

bill.summers (6/10/2009)
I have some name and address data that has the city and state in one column. I have created a state column and moved the states into that column. Now I can't figure out how to get the State part of City/State out of that column so I just have the city in it. Typical data is like Chicago Il or New York Ny.
Thanks,
Bill

You can use string functions to get rid of the last three characters (space plus the two letter state code). For example:

declare @data varchar(100)

set @data = 'San Francisco CA'

select LEFT(@data, datalength(@data)-3)

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

taosun SSC-Addicted Points: 414 More actions · Answer 2

Andras,

Thanks for the reply. I'm new at this so would you mind going on and telling me exactly how to update this field in several thousand records. Say the database name is ABC and the table name is DEF and the column name is City.

Thanks in advance.

Bill

Lowell SSC Guru Points: 323518 More actions · Answer 3

Andras gave you an example; we thought you could extrapolate.

note the assumption that with the data, there is always space and then the two digit state code.

here is a more detailed example:

create table #example(Cityname varchar(100),StateCode varchar(2),exampletext varchar(100) )

insert into #example(exampletext)

SELECT 'San Francisco CA' UNION ALL

SELECT 'Chicago IL' UNION ALL

SELECT 'New York NY'

select LEFT(exampletext, datalength(exampletext)-3) As CityName,

RIGHT(exampletext, 2) As StateCode

from #example

--results

CityName StateCode

-------------- ---------

San Francisco CA

Chicago IL

New York NY

--now an update example

UPDATE #example

SET Cityname = LEFT(exampletext, datalength(exampletext)-3),

StateCode = RIGHT(exampletext, 2)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dk7525 SSC Enthusiast Points: 131 More actions · Answer 4

Hi ,

Here is sample solution. Just you need to add the loop for this solution.

Here I am taking only one string example.

Declare @temp2 table

(

city varchar(20),

state varchar(20)

)

Declare @string varchar(20)

set @string='NewYork,USA'

Declare @i int

select @i=charindex(',',@string)

Declare @length int

select @length=len(@string)

Declare @C varchar(20)

select @C=substring(@string,@i+1,@length)

Declare @d varchar(20)

select @d=substring(@string,1,@i-1)

Insert into @temp2(city,state)

select @d,@c

select * from @temp2

Find the Particular character and split in to the two substring.