Comma Separation Column - SQL Server Forums

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

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Comma Separation Column

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-03-15 : 20:57:42

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

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-03-24 : 19:39:48

create the below function in you database

create FUNCTION retrive
(@in char(50),@Outpos int)
RETURNS char(50)
AS
begin
declare @out char(50)
declare @pos int
set @pos = 0
declare @indx int
set @indx = 1

set @in = rtrim(@in) + ', ,'
while @indx < len(@in) and @pos < @outpos
begin
select @out = substring(@in,@indx,charindex(',',@in,@indx) - @indx)
select @indx = charindex(',',@in,@indx) + 1,@pos = @pos + 1
end

return @out
end

-----------------------------------------
then simply:
select dbo.retrive(description,1) as col1,
dbo.retrive(description,2) as col2
dbo.retrive(description,3) as col3
dbo.retrive(description,4) as col4
from ...

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-03-24 : 19:40:46

not what you asked for but much easier to read and write!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources