SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 help needed to separate string in a cell
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

suety29
Starting Member

Saint Lucia
5 Posts

Posted - 01/25/2013 :  09:22:50  Show Profile  Reply with Quote
i am using ssis to transfer data from an old database structure to a new one. in the old table there's a cell where values from a multiple select checkbox was stored as '12345'. However in the new structure the values are comma separated for e.g. '1,2,3,4,5'.

i cant seem to figure out how to separate the string using commas. Can anyone help??? also note that the length of the string varies from row to row as follows:

row 1 may have - '135'
row 2 may have - '12345'
row 3 may have - '2'

etc.....

Thanks!

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/25/2013 :  09:57:38  Show Profile  Reply with Quote
i would do this in t-sql as that will be set based and will be calling the script/procedure from execute sql task in ssis.

the script would be like

CREATE PROCEDURE StuffChar
@String varchar(100),
@Char   char(1),
@Stuffed varchar(150) OUTPUT
AS

;With CTE
AS
(
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM CTE
WHERE N + 1 <= LEN(@String)
)
SELECT @Stuffed= COALESCE(@Stuffed,'') + SUBSTRING(@String,N,1) + @Char
FROM CTE

SET @Stuffed = STUFF(@Stuffed,LEN(@Stuffed),1,'')
GO

then execute it like

DECLARE @Str varchar(200)
EXEC StuffChar '76213816439827432094730921480',',',@Str OUT

SELECT @Str


output
-------------------------------------------------------------
7,6,2,1,3,8,1,6,4,3,9,8,2,7,4,3,2,0,9,4,7,3,0,9,2,1,4,8,0



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

Go to Top of Page

suety29
Starting Member

Saint Lucia
5 Posts

Posted - 01/28/2013 :  21:49:26  Show Profile  Reply with Quote
Thanks for your help.

I'm kind of a newbie with transact sql so forgive me if this question sounds a bit dumb. How do you implement it for a query that returns multiple rows? for e.g. the data comes from a table called 'Test' and 'string' is the field i would like to separate. So if i have a query such as "select string from test" and it returns 10 rows, how do i loop through the rows?
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 01/29/2013 :  01:35:00  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
quote:
Originally posted by suety29

Thanks for your help.

I'm kind of a newbie with transact sql so forgive me if this question sounds a bit dumb. How do you implement it for a query that returns multiple rows? for e.g. the data comes from a table called 'Test' and 'string' is the field i would like to separate. So if i have a query such as "select string from test" and it returns 10 rows, how do i loop through the rows?





Do you need this?

Declare @listStr varchar(max)
Select @listStr =COALESCE(@listStr+',', '')+ string from test
select @listStr

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/29/2013 :  02:53:05  Show Profile  Reply with Quote
quote:
Originally posted by suety29

Thanks for your help.

I'm kind of a newbie with transact sql so forgive me if this question sounds a bit dumb. How do you implement it for a query that returns multiple rows? for e.g. the data comes from a table called 'Test' and 'string' is the field i would like to separate. So if i have a query such as "select string from test" and it returns 10 rows, how do i loop through the rows?



use select query as a source for execute sql task and store result in variable of type object
Then add a for each loop to iterate through object variable using recordset enumerator and inside loop add a string variable to get value for each iteration. Then call the sp in execute sql task passing individual values from variable.

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

Go to Top of Page

tm
Posting Yak Master

160 Posts

Posted - 01/29/2013 :  15:26:16  Show Profile  Reply with Quote
Here is another option ..
1. Create function in database
2. use data flow task and use T-SQL to use function to return string with comma

This will get you the correct data and you can then export to the new table.

-- ====================================
-- == Create function in database
-- ====================================
create function dbo.testAddComma(@inputStr varchar(500))
returns varchar(1000)
as
begin
declare @ipos int
declare @outputStr varchar(1000)

set @ipos = LEN(@inputStr) -1

while @ipos > 0
begin
select @inputStr = left(@inputStr, @ipos) + ',' + RIGHT(@inputStr, LEN(@inputStr) - @ipos)
select @ipos -= 1
end

return(@inputStr)
end

--==========================================
-- T-SQL in Data Flow task to return modified string
-- change column names and table name to your table info

select tid, dbo.testAddComma([str]) as newStr
from @table
Go to Top of Page

suety29
Starting Member

Saint Lucia
5 Posts

Posted - 01/30/2013 :  16:42:53  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by suety29

Thanks for your help.

I'm kind of a newbie with transact sql so forgive me if this question sounds a bit dumb. How do you implement it for a query that returns multiple rows? for e.g. the data comes from a table called 'Test' and 'string' is the field i would like to separate. So if i have a query such as "select string from test" and it returns 10 rows, how do i loop through the rows?



use select query as a source for execute sql task and store result in variable of type object
Then add a for each loop to iterate through object variable using recordset enumerator and inside loop add a string variable to get value for each iteration. Then call the sp in execute sql task passing individual values from variable.

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





Hi visakh16,

i'm not familiar with using the execute sql task in ssis since i'm actually just starting out using integration services. I tried implementing your suggestion but i don't quite understand how to do so. The old table has the following fields airline, flight#, startdate, enddate, eta, dow, port, inactive. What i want to do is import these fields into a new table but during this import, i need to change the string in the 'dow' (1234567) field to the comma separated field (1,2,3,4,5,6,7). So records have no data in this field, some may have one character, etc. Can you break down your suggestion into steps?

Go to Top of Page

suety29
Starting Member

Saint Lucia
5 Posts

Posted - 01/30/2013 :  16:47:01  Show Profile  Reply with Quote
quote:
Originally posted by tm

Here is another option ..
1. Create function in database
2. use data flow task and use T-SQL to use function to return string with comma

This will get you the correct data and you can then export to the new table.

-- ====================================
-- == Create function in database
-- ====================================
create function dbo.testAddComma(@inputStr varchar(500))
returns varchar(1000)
as
begin
declare @ipos int
declare @outputStr varchar(1000)

set @ipos = LEN(@inputStr) -1

while @ipos > 0
begin
select @inputStr = left(@inputStr, @ipos) + ',' + RIGHT(@inputStr, LEN(@inputStr) - @ipos)
select @ipos -= 1
end

return(@inputStr)
end

--==========================================
-- T-SQL in Data Flow task to return modified string
-- change column names and table name to your table info

select tid, dbo.testAddComma([str]) as newStr
from @table




Hi tm,

Thanks for you suggestion. I tried it but it doesn't give the expected result in all cases. For instance if the field has a string of 1356, the result of your function would be 1,,, but if the string is 1234567, it displays 1,2,3,4,5,6,7 which is correct.
Go to Top of Page

tm
Posting Yak Master

160 Posts

Posted - 01/31/2013 :  13:47:11  Show Profile  Reply with Quote
Hi Suety,

My mistake in not using RTRIM for Char data type string. Tested using varchar data type

Here is the modified function to include RTRIM. This should fix the function to return correct string.

CREATE function [dbo].[testAddComma](@inputStr varchar(500))
returns varchar(1000)
as
begin
declare @ipos int
declare @outputStr varchar(1000)

set @ipos = LEN(RTRIM(@inputStr)) -1

while @ipos > 0
begin
select @inputStr = left(RTRIM(@inputStr), @ipos) + ',' + RIGHT(RTRIM(@inputStr), LEN(RTRIM(@inputStr)) - @ipos)
select @ipos -= 1
end

return(@inputStr)
end
Go to Top of Page

suety29
Starting Member

Saint Lucia
5 Posts

Posted - 02/01/2013 :  14:30:55  Show Profile  Reply with Quote
quote:
Originally posted by tm

Hi Suety,

My mistake in not using RTRIM for Char data type string. Tested using varchar data type

Here is the modified function to include RTRIM. This should fix the function to return correct string.

CREATE function [dbo].[testAddComma](@inputStr varchar(500))
returns varchar(1000)
as
begin
declare @ipos int
declare @outputStr varchar(1000)

set @ipos = LEN(RTRIM(@inputStr)) -1

while @ipos > 0
begin
select @inputStr = left(RTRIM(@inputStr), @ipos) + ',' + RIGHT(RTRIM(@inputStr), LEN(RTRIM(@inputStr)) - @ipos)
select @ipos -= 1
end

return(@inputStr)
end



Thanks guys for all the help!

TM it worked! Thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000