splitting a column in to 3 columns

Question

Post reply

splitting a column in to 3 columns

gupta.sreekanth

SSC-Addicted

Points: 483
More actions
February 28, 2011 at 11:39 pm

#231946

hi

can any one help me in splitting like this in ssis

bas-06-12586

to

bas

06

12586

in different columns

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

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

Dehqon D. SSC Eights! Points: 905 More actions · Answer 1

Dehqon D.

SSC Eights!

Points: 905

March 1, 2011 at 12:09 am

#1292738

Hi gupta.sreekanth,

I'm not that expert in scripting, but for the starting u may use following i guess:

Select SUBSTRING(dbo.Table.Column, 0, CHARINDEX('-', dbo.Table.Column, 1,)) as Column1

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

gupta.sreekanth (2/28/2011)

hi

can any one help me in splitting like this in ssis

bas-06-12586

to

bas

06

12586

in different columns

In SSIS you can use the Derived Column Data Flow Transformation in your Data Flow Task. This will allow you to run an expression against the incoming column containing the dash-delimited strings to create three output columns.

Within the Derived Column Data Flow Transformation SSIS provides a decent expression-builder with functions like FINDSTRING, LEN and SUBSTRING that will support this effort.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

madhuri-425013 Say Hey Kid Points: 679 More actions · Answer 3

Hi,

You can use the below query in the ssis.

I have taken your string in to one variable and splitted the string in to 3 columns.

There are two methods to derive this

1)Using substring , patindex

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

declare @ssn varchar(50)

set @ssn = 'bas-06-12586'

select @ssn,

substring(@ssn,0,patindex('%-[0-9]%',@ssn)) as first,

substring(substring(@ssn,patindex('%-[0-9]%',@ssn)+1,len(@ssn) - patindex('%-[0-9]%',@ssn)),

0,

patindex('%-[0-9]%',substring(@ssn,patindex('%-[0-9]%',@ssn)+1,len(@ssn) - patindex('%-[0-9]%',@ssn)))) as second,

substring(substring(@ssn,patindex('%-[0-9]%',@ssn)+1,len(@ssn) - patindex('%-[0-9]%',@ssn)),

4,

len(substring(@ssn,patindex('%-[0-9]%',@ssn)+1,len(@ssn) - patindex('%-[0-9]%',@ssn)))) as third

2)Using Substring, patindex, reverse

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

declare @ssn varchar(50)

set @ssn = 'bas-06-12586'

select @ssn,

reverse(substring(substring(reverse(@ssn),patindex('%-[0-9]%',reverse(@ssn))+1,len(reverse(@ssn))-patindex('%-[0-9]%',reverse(@ssn))),

patindex('%-[a-z]%',substring(reverse(@ssn),patindex('%-[0-9]%',reverse(@ssn))+1,len(reverse(@ssn))-patindex('%-[0-9]%',reverse(@ssn))))+1,

len(substring(reverse(@ssn),patindex('%-[0-9]%',reverse(@ssn))+1,len(reverse(@ssn))-patindex('%-[0-9]%',reverse(@ssn)))))) as first,

reverse(substring(substring(reverse(@ssn),patindex('%-[0-9]%',reverse(@ssn))+1,len(reverse(@ssn))-patindex('%-[0-9]%',reverse(@ssn))),

patindex('%[0-9][0-9]-%',substring(reverse(@ssn),patindex('%-[0-9]%',reverse(@ssn))+1,len(reverse(@ssn))-patindex('%-[0-9]%',reverse(@ssn)))),

patindex('%-[a-z]%',substring(reverse(@ssn),patindex('%-[0-9]%',reverse(@ssn))+1,len(reverse(@ssn))-patindex('%-[0-9]%',reverse(@ssn))))

- patindex('%[0-9][0-9]-%',substring(reverse(@ssn),patindex('%-[0-9]%',reverse(@ssn))+1,len(reverse(@ssn))-patindex('%-[0-9]%',reverse(@ssn))))

)) as second,

reverse(substring(reverse(@ssn),0,patindex('%-[0-9]%',reverse(@ssn)))) as third

You can use any of the method and can try in SSIS.

Hope this might solve your problem.

Thanks,

Madhuri

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

If the expected format of the source data is always the same, this is a simple way to do it...

DECLARE @SplitString VARCHAR(100)



 SELECT @SplitString = 'bas-06-12586'

 SELECT Col1 = PARSENAME(REPLACE(@SplitString,'-','.'),3),

        Col2 = PARSENAME(REPLACE(@SplitString,'-','.'),2),

        Col3 = PARSENAME(REPLACE(@SplitString,'-','.'),1)

You can use a column name in place of @SplitString just as easily...

SELECT Col1 = PARSENAME(REPLACE(YourColumn,'-','.'),3),



        Col2 = PARSENAME(REPLACE(YourColumn,'-','.'),2),

        Col3 = PARSENAME(REPLACE(YourColumn,'-','.'),1)

   FROM dbo.YourTable

--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)
Intro to Tally Tables and Functions

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 5

Jeff Moden (3/2/2011)

If the expected format of the source data is always the same, this is a simple way to do it...

DECLARE @SplitString VARCHAR(100)
SELECT @SplitString = 'bas-06-12586' SELECT Col1 = PARSENAME(REPLACE(@SplitString,'-','.'),3), Col2 = PARSENAME(REPLACE(@SplitString,'-','.'),2), Col3 = PARSENAME(REPLACE(@SplitString,'-','.'),1)

You can use a column name in place of @SplitString just as easily...

SELECT Col1 = PARSENAME(REPLACE(YourColumn,'-','.'),3),
Col2 = PARSENAME(REPLACE(YourColumn,'-','.'),2), Col3 = PARSENAME(REPLACE(YourColumn,'-','.'),1) FROM dbo.YourTable

That is a very interesting method. (I had never heard of the PARSENAME function before).

Another one for my knowledge base 🙂

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

grahamc SSCertifiable Points: 6762 More actions · Answer 6

Koen Verbeeck (3/2/2011)

Jeff Moden (3/2/2011)

If the expected format of the source data is always the same, this is a simple way to do it...

DECLARE @SplitString VARCHAR(100)
SELECT @SplitString = 'bas-06-12586' SELECT Col1 = PARSENAME(REPLACE(@SplitString,'-','.'),3), Col2 = PARSENAME(REPLACE(@SplitString,'-','.'),2), Col3 = PARSENAME(REPLACE(@SplitString,'-','.'),1)

You can use a column name in place of @SplitString just as easily...

SELECT Col1 = PARSENAME(REPLACE(YourColumn,'-','.'),3),
Col2 = PARSENAME(REPLACE(YourColumn,'-','.'),2), Col3 = PARSENAME(REPLACE(YourColumn,'-','.'),1) FROM dbo.YourTable

That is a very interesting method. (I had never heard of the PARSENAME function before).

Another one for my knowledge base 🙂

Me too 😀

Just having a play with the statement now.... nice and simple :hehe:

Jeff strikes again :hehe:

gupta.sreekanth SSC-Addicted Points: 483 More actions · Answer 7

gupta.sreekanth

SSC-Addicted

Points: 483

March 2, 2011 at 8:02 am

#1293522

thanks a ton......................

gupta.sreekanth SSC-Addicted Points: 483 More actions · Answer 8

gupta.sreekanth

SSC-Addicted

Points: 483

March 2, 2011 at 8:02 am

#1293523

thanks a ton......................

gupta.sreekanth SSC-Addicted Points: 483 More actions · Answer 9

gupta.sreekanth

SSC-Addicted

Points: 483

March 2, 2011 at 8:03 am

#1293524

thanks a ton......................

gupta.sreekanth SSC-Addicted Points: 483 More actions · Answer 10

gupta.sreekanth

SSC-Addicted

Points: 483

March 2, 2011 at 8:04 am

#1293525

thanks a ton......................

gupta.sreekanth SSC-Addicted Points: 483 More actions · Answer 11

gupta.sreekanth

SSC-Addicted

Points: 483

March 2, 2011 at 8:04 am

#1293527

thanks a ton......................

Ryan Fitzgerald SSC Rookie Points: 42 More actions · Answer 12

Ryan Fitzgerald

SSC Rookie

Points: 42

March 2, 2011 at 8:04 am

#1293528

Very smart method, I dig it!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Koen Verbeeck (3/2/2011)

That is a very interesting method. (I had never heard of the PARSENAME function before).

Another one for my knowledge base 🙂

grahamc (3/2/2011)

Me too 😀

Just having a play with the statement now.... nice and simple :hehe:

Jeff strikes again :hehe:

Ryan Fitzgerald (3/2/2011)

Very smart method, I dig it!

Thanks for the feedback, folks. Simple 2, 3, or 4 part splits seem to come up a lot. What'cha figure? "SQL Spackle" article worthy?

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

gupta.sreekanth (3/2/2011)

thanks a ton......................

You bet... thank you for the feedback.

--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)
Intro to Tally Tables and Functions