How to seperate info in 1 field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to seperate info in 1 field

Author

Topic

jatrix32
Starting Member

49 Posts

Posted - 2010-09-03 : 12:21:30

I am trying to take info that is in 1 field and break it into 2.The problem is that the info in the field is all different sizes.

The info is an address. for example

change street, 44319
someother street, 44444

so it is a street name and a zip separated by a comma.

is there sql code that can grab this field, and use the comma as a delimiter and put the values into 2 different fields?

Not sure if I explained this well, Thanks in advance for any help.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-03 : 13:38:45

try this:


declare @string varchar(100)
set @string =  'change street, 44319'
--set @string = 'someother street, 44444'

select @string
,substring(@string,1,charindex(',',@string)-1) as street_name
,ltrim(rtrim(substring(@string,charindex(',',@string)+1,len(@string)))) as street_number

jatrix32
Starting Member

49 Posts

Posted - 2010-09-03 : 13:42:42

Does this work for multiple records or just 1? The table has 20,000 records in it.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-03 : 13:59:44

it works with as many records as you have.

this is just a sample. use:

,substring(@string,1,charindex(',',@string)-1) as street_name
,ltrim(rtrim(substring(@string,charindex(',',@string)+1,len(@string)))) as street_number

in your query and replace @string with the name of your column in your table.

jatrix32
Starting Member

49 Posts

Posted - 2010-09-03 : 14:05:28

Do I need to use the declare? it looks like in the declare statement, there is hard coded information:

set @string = 'change street, 44319'
--set @string = 'someother street, 44444'

jatrix32
Starting Member

49 Posts

Posted - 2010-09-03 : 15:32:47

I need to update my table by taking information in one field and populating 2 other field with it. I have about 20,000 records in the table. The field I want to separate is called address. it contains a city and a zip code ie... cityname zip. I want to split it and funnel the cityname into a new column called city and funnel zip into a new column called zip.

I was not very clear in my original post, sorry.

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-09-04 : 02:03:36

Hai Jatrix32,

Try this

Update Table
Set Address=Parsename(Replace(string ,',','.'),2),
Zip=Parsename(Replace(@string ,',','.'),1)

Select Parsename(Replace(@string ,',','.'),2),Parsename(Replace(@string ,',','.'),1)

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-04 : 03:30:25

@jatrix32: please post your table schema, DDL and which field are you trying to update.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-06 : 05:04:13

quote:
Originally posted by nirene

Hai Jatrix32,

Try this

Update Table
Set Address=Parsename(Replace(string ,',','.'),2),
Zip=Parsename(Replace(@string ,',','.'),1)

Select Parsename(Replace(@string ,',','.'),2),Parsename(Replace(@string ,',','.'),1)

It should be

Update Table
Set Address=Parsename(Replace(string ,',','.'),2),
Zip=Parsename(Replace(string ,',','.'),1)

Madhivanan

Failing to plan is Planning to fail

jatrix32
Starting Member

49 Posts

Posted - 2010-09-07 : 09:17:15

The above code took the info from my address field and put it all into the zip code field, and put nothing in the city field.

Here is my table info. I have an address field that contains both a city and a zip, separated by a space. I am looking for sql too update my table so that the address field is split and the city populates my city field and the zip populates my zip field. As I said, bot are currently contained in the address field. So I have address, city and zip field headers.

The info looks like this: JEFFERSON 30549

I cant figure this out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources