How to separate one attribute (zip code from city) in 2 columns?

Question

Post reply

How to separate one attribute (zip code from city) in 2 columns?

Jagger-784276

SSC Eights!

Points: 808
More actions
July 14, 2009 at 8:43 am

#206972

Hi, I need help. I have table where one attribute has name zip code and city. It looks like :
Zip code and city
60601 Chicago
10292 New York
How can I separate one attribute to 2 attributes in order to get
zip code ; city
60601 ; Chicago
10292 ; New York
Do u have any examples from integration services.
I know that I need OLE db Source and OLE Ddb Destination but what do I have to put in data flow how could I separete it one attributes to two?
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

declare @str1 varchar(100)

set @str1='60601 Chicago'

select left(@str1,charindex(' ',@str1)-1) as ZIP, right(@str1,len(@str1)-charindex(' ',@str1))as City

OUTPUT:-

----------

ZIP City

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

60601 Chicago

(1 row(s) affected)

Assumption: ZIP and city are seperated by a space.

Pradeep Singh

Jagger-784276 SSC Eights! Points: 808 More actions · Answer 2

thanks, but how to do it in integration services?

Which data flow transformation I have to use?

ps. One Orange Chip Points: 29422 More actions · Answer 3

Well, Not much idea of how u implement it in integration services.

Someone with better knowledge should help you out 🙂

Pradeep Singh

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

Two derived columns seems like the easiest way to me.

If you need help writing the expressions, post back. But give it a go, it's not too difficult.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jagger-784276 SSC Eights! Points: 808 More actions · Answer 5

Hi, thanks. I am begginer and really don't know how to do it.

Just I have table with customer data, where I have column zip code and city.

I'd like to create new table with customer data where this column should be in two new columns separated.

If u can help me, good.

In this column I have hundreds and hundreds rows.

regards

ps. One Orange Chip Points: 29422 More actions · Answer 6

Jagger (7/14/2009)
Just I have table with customer data, where I have column zip code and city.
I'd like to create new table with customer data where this column should be in two new columns separated.

If you aim to seperate these two fields and create a seperate table, you can do it without SSIS package by a simple query.

Select fld1, fld2, fld3, left(fldMixed,charindex(' ',fldMixed)-1) as ZIP, right(fldMixed,len(fldMixed)-charindex(' ',fldMixed))as City into NewTable from Oldtable

You can take help of this code to create your new table!!

where fldMixed is the field containing zip and city names in the source table.

fld1, fld2.... are your other fields.

Pradeep Singh

Jagger-784276 SSC Eights! Points: 808 More actions · Answer 7

It doesn't work.

What I did?

Select left('zip and city',charindex(' ',''zip and city'')-1) as ZIP,

right(''zip and city'',len(''zip and city'')-charindex(' ',''zip and city''))as City

into NewCustomer

from customer

and I got 2 derived columns zip,city

and all rows contains zip in zip and city in city

it looks like

zip city

I didn't get

zip _________________city

60601_______________Chicago

60602_______________Chicago

10292_______________New York

why?

ps. One Orange Chip Points: 29422 More actions · Answer 8

--1. create the table

create table customer

(

[zip and city] varchar (50)

)

--2. insert sample data

insert into customer

select '60601 Chicago'

union

select '60602 Chicago'

union

select '10292 New York'

--3. parse and insert data to new table

select left([zip and city],charindex(' ',[zip and city])-1) as ZIP, right([zip and city],len([zip and city])-charindex(' ',[zip and city]))as City

into NewCustomer

from customer

--4. check it

select * from newcustomer

Output

------

ZIP City

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

10292 New York

60601 Chicago

60602 Chicago

(3 row(s) affected)

use the 3rd query mentioned above to create the table. I just tested and it works fine.

Remember, newcustomer table has already been created so you need to drop it before running the query

drop table newcustomer

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 9

Jagger (7/14/2009)
It doesn't work.
What I did?
Select left('zip and city',charindex(' ',''zip and city'')-1) as ZIP,
right(''zip and city'',len(''zip and city'')-charindex(' ',''zip and city''))as City
into NewCustomer
from customer
why?

because you havent mentioned your columns properly in the query. since the columns contain multiple spaces, you need to put the column name within []

Replace this with actual column name or as i've mentioned in the previous post.

'zip and city'

Pradeep Singh