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

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?

---

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?

---

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/

---

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

---

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

---