Split Comma Seperate Column Problem

Question

Split Comma Seperate Column Problem

jscot91

SSC Veteran

Points: 294
More actions
March 15, 2011 at 7:02 pm

#236762

Here is my source data
Description
D-1,D2
D-2,D-U,T1
U-5,T2,K-5,T3
NULL
Here is the expressions i am using in "Derived Column"
Col1 = SUBSTRING(Description,1,FINDSTRING(Description,",",1) - 1)
Col2 = SUBSTRING(Description,FINDSTRING(Description,",",1) + 1,FINDSTRING(Description,",",2) - FINDSTRING(Description,",",1) - 1)
Col3 = SUBSTRING(Description,FINDSTRING(Description,",",2) + 1,LEN(Description))
Col4 = Need help for expression
Note:- I am receiving error "Disposition" Error. Please help me out where i am wrong. Thanks in advance

Viewing 15 posts - 1 through 15 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

jscot91 (3/15/2011)
Here is my source data
Description
D-1,D2
D-2,D-U,T1
U-5,T2,K-5,T3
NULL
Here is the expressions i am using in "Derived Column"
Col1 = SUBSTRING(Description,1,FINDSTRING(Description,",",1) - 1)
Col2 = SUBSTRING(Description,FINDSTRING(Description,",",1) + 1,FINDSTRING(Description,",",2) - FINDSTRING(Description,",",1) - 1)
Col3 = SUBSTRING(Description,FINDSTRING(Description,",",2) + 1,LEN(Description))
Col4 = Need help for expression
Note:- I am receiving error "Disposition" Error. Please help me out where i am wrong. Thanks in advance

No reflection on you but a lot of folks make some pretty good mistakes in their functions without even realizing it. Please post the code for the FINDSTRING function you're using.

Also, please verify that you're using SQL Server 2005.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jscot91 SSC Veteran Points: 294 More actions · Answer 2

Thanks for prompt reply, I am using SQL Server 2008. I am sorry i don't understand which code? I am using SSIS and this expression in derived column transformation. Thanks.

Gurulakshmanaprabhu Gurusamy SSC Enthusiast Points: 142 More actions · Answer 3

Hi,

Data of this kind could be broke down using split function available in VB.Net.Hence instead of using derived column transformation with substring function , same result would be obtained by using script component by chopping input string using delimiter (comma in your case).

Thanks and Regards,

Gurulakshmanaprabhu Gurusamy

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 4

Gurulakshmanaprabhu Gurusamy (3/15/2011)
Hi,
Data of this kind could be broke down using split function available in VB.Net.Hence instead of using derived column transformation with substring function , same result would be obtained by using script component by chopping input string using delimiter (comma in your case).
Thanks and Regards,
Gurulakshmanaprabhu Gurusamy

Yes it could. Why anyone would need to step outside of SQL for a simple problem like this is another story. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 5

jscot91,

We need to know something to wrap it up with code, though. What is the code for FINDSTRING?

We're just trying to help here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Description

D-1,D2

D-2,D-U,T1

U-5,T2,K-5,T3

NULL

Is that real sample data? Or have you tidied it up to remove trailing commas, perhaps?

If it is real, you are going to get errors with your derived columns in cases where there are fewer than four fields.

One option may be to pre-process the file and add in the necessary number of trailing commas, then let SSIS import it as a standard CSV file. Then SSIS gets to do the parsing for you.

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

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

Jeff

FINDSTRING is a built-in SSIS function, see here.

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 8

Phil Parkin (3/16/2011)
Jeff
FINDSTRING is a built-in SSIS function, see here.

Ah! Thanks, Phil... That's what I get when I post on a forum for something I don't use. :blush: It appears to be similar to CHARINDEX in T-SQL with the added sophistication of being able to indentify which occurance you want to find. Thanks again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 9

Ok... I'm pretty much ignorant of SSIS. My question at this point is why couldn't one of the common split functions written in T-SQL work here for the OP?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

Jeff Moden (3/16/2011)
Ok... I'm pretty much ignorant of SSIS. My question at this point is why couldn't one of the common split functions written in T-SQL work here for the OP?

Because you can't use a function from SQL in a derived column, and to try to do so would interrupt the stream, forcing a block at a point, slowing the process down.

OP: Can you post the full error you're getting in the debug output? Or from the errors list if it's not compiling at all?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

Nevermind, I see the problem. You don't have evaluations in your strings, as Phil mentioned earlier. You need to test findstring to see if it found the character

IE: FINDSTRING( col, ",", 3) != 0 (Yes, SSIS still uses this archaic form of not equals)

then use the if/then/else structure to deal with it:

fxn == 0 ? dt_null : substring()

Replace FXN with your finding function, the substring with the extractor function, and (dt_null) with the nullable datatype version of whatever string format you want in the string (you can find them in the datatypes node in the upper right).

You may, if you don't have trailing commas, need to test the tail of the string in a nested if function (which is described by the ? : structure) to see what form of the substring function you'll need to use.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 12

Craig Farrell (3/16/2011)
Jeff Moden (3/16/2011)
Ok... I'm pretty much ignorant of SSIS. My question at this point is why couldn't one of the common split functions written in T-SQL work here for the OP?
Because you can't use a function from SQL in a derived column, and to try to do so would interrupt the stream, forcing a block at a point, slowing the process down.

Really?! Don't ya just love the interoperability between SQL Server products? 😛 Thanks, Craig.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Yes, SSIS still uses this archaic form of not equals

Not that archaic ... so does C#.NET 🙂

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 14

Jeff Moden (3/16/2011)
jscot91,
We need to know something to wrap it up with code, though. What is the code for FINDSTRING?
We're just trying to help here.

Jeff, stop interfering in SSIS threads. 😛 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP