Help in spliting comma delimited column

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

Help in spliting comma delimited column

Author

Topic

nasman
Starting Member

15 Posts

Posted - 2012-05-17 : 04:49:38

Hi SQL Peeps,

I need help on this, i just need to split a comma delimited column into 2 seperate column and in the same row, is this possible? im extracting this data on a csv file,

**my extracted data looks like this

COL1_OPERATOR COL2_SALES PROMO_ID
------------- ---------- --------
>= or =< 200,500 123

**the raw file looks like this

"COL1_OPERATOR","COL2_SALES","PROMO_ID"
">=or=<","200,500","123"

**The desired result looks like this

COL1_OPERATOR COL2_SALES1 COL2_SALES2 PROMO_ID
------------- ---------- ----------- --------
>= or =< 200 500 123

any help or suggestion is much appreciated

cheers

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-17 : 07:34:21

Did you try something to achieve this?

Vijay is here to learn something from you guys.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-17 : 07:44:42

[code]

As records are in comma separated so you can SSIS packages to load the data in you destination table. But make sure for "COL2_SALES" in your raw file should be two values separated with commas.

One more approach:

create table data (COL1_OPERATOR varchar(20),COL1_SALES1 varchar(20),
COL2_SALES2 varchar(20),
PROMO_ID varchar(20))

bulk insert data from 'C:\SSISLoad\Data.txt'
with( DATAFILETYPE = 'char',
rowterminator= '\n',
Fieldterminator =',')

update data set COL1_OPERATOR = REPLACE(COL1_OPERATOR,'"',''),
COL1_SALES1 =REPLACE(COL1_SALES1,'"',''),
COL2_SALES2=REPLACE(COL2_SALES2,'"',''),
PROMO_ID= REPLACE(PROMO_ID,'"','')

select * from data

[/code]

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-19 : 15:14:24

why not use format file and give correct column delimiter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources