how to separate a string

[sruthi]

Hi,

Can any one tell me how to separate a string with comma(,) with ','



eg:'a,b,c' to 'a','b','c'



Thanks

Neelima

[pradyut.dhara]

Hi,
Try this one if it works

Code:

create procedure char_separator(
                                @input varchar(1024),
                                @separator char,
                                @output varchar(1024) OUT)  
as
declare @pos int,
        @aChar varchar(1024)
        
if right(rtrim(@input),1) <> @separator
    set @input = @input  + @separator

set @pos =  charindex(@separator , @input)
while @pos <> 0
begin
    set @aChar = left(@input, @pos - 1)
    set @input = stuff(@input, 1, @pos, null)
    set @pos =  charindex(@separator , @input)
    if @pos=0
        set @output=  @output + @aChar
    else
        set @output=  @output+ @aChar + @separator
end
go

declare @output varchar(1024)
exec char_separator 'A,B,C',',',@output OUTPUT
go
(return status = 0)

Return parameters:
--------------------------------
A,B,C

[DerekAsirvadem]

Sruthi

I think you mean: how does one break up a string, which is a concatenated list of comma-separated values, into separate elements. Is that right ?

No problem. Easy as pie.

But there are constraints on the coding, which must be answered first.

1. Do you always have 3 elements, or is the number of elements variable ?

2. If the no of elements is variable, what is the maximun number ?

3. Do you want a proc or a function ? or just an SQL code segment ?

4. When you call the proc or function, of course @input will contain the single comma-separated string. How would you like the multiple elements produced, to be output ? Do you want @out_1, @out_2, @out_3, @out_99 ? Or would you like a single-column result set containing single elements to be returned ?

---

(11-10-2011 02:40 PM)pradyut.dhara Wrote:  Try this one if it works

That certainly does what has been asked of it.

What do you get with:

Code:

DECLARE @input VARCHAR(1024), @output VARCHAR(1024)
SET @input = 'A,B,C'
EXEC char_separator @input, ',' ,@output OUTPUT
IF @input = @output
    PRINT "Output = Input: Null Proc"
ELSE
    PRINT "Output != Input: Proc error"
go

[sukheshnair]

Not sure if this will work ... Try it ... You can program it if you want.

select stuff ('a,b,c', 2, 4, "','b','c'" )

Cheers
Sukhesh

(11-11-2011 05:06 PM)DerekAsirvadem Wrote:  Sruthi

I think you mean: how does one break up a string, which is a concatenated list of comma-separated values, into separate elements. Is that right ?

No problem. Easy as pie.

But there are constraints on the coding, which must be answered first.

1. Do you always have 3 elements, or is the number of elements variable ?

2. If the no of elements is variable, what is the maximun number ?

3. Do you want a proc or a function ? or just an SQL code segment ?

4. When you call the proc or function, of course @input will contain the single comma-separated string. How would you like the multiple elements produced, to be output ? Do you want @out_1, @out_2, @out_3, @out_99 ? Or would you like a single-column result set containing single elements to be returned ?

---

(11-10-2011 02:40 PM)pradyut.dhara Wrote:  Try this one if it works

That certainly does what has been asked of it.

What do you get with:

Code:

DECLARE @input VARCHAR(1024), @output VARCHAR(1024)
SET @input = 'A,B,C'
EXEC char_separator @input, ',' ,@output OUTPUT
IF @input = @output
    PRINT "Output = Input: Null Proc"
ELSE
    PRINT "Output != Input: Proc error"
go

---

Ohhh ... just found another function (not sure if this is in new releases) ...

select str_replace ('a,b,c',",","','")

Worth a try.

Cheers
Sukhesh

(11-14-2011 03:15 PM)sukheshnair Wrote:  Not sure if this will work ... Try it ... You can program it if you want.

select stuff ('a,b,c', 2, 4, "','b','c'" )

Cheers
Sukhesh

(11-11-2011 05:06 PM)DerekAsirvadem Wrote:  Sruthi

I think you mean: how does one break up a string, which is a concatenated list of comma-separated values, into separate elements. Is that right ?

No problem. Easy as pie.

But there are constraints on the coding, which must be answered first.

1. Do you always have 3 elements, or is the number of elements variable ?

2. If the no of elements is variable, what is the maximun number ?

3. Do you want a proc or a function ? or just an SQL code segment ?

4. When you call the proc or function, of course @input will contain the single comma-separated string. How would you like the multiple elements produced, to be output ? Do you want @out_1, @out_2, @out_3, @out_99 ? Or would you like a single-column result set containing single elements to be returned ?

---

(11-10-2011 02:40 PM)pradyut.dhara Wrote:  Try this one if it works

That certainly does what has been asked of it.

What do you get with:

Code:

DECLARE @input VARCHAR(1024), @output VARCHAR(1024)
SET @input = 'A,B,C'
EXEC char_separator @input, ',' ,@output OUTPUT
IF @input = @output
    PRINT "Output = Input: Null Proc"
ELSE
    PRINT "Output != Input: Proc error"
go