You are not logged in or registered. Please login or register to use the full functionality of this Website SybaseTeam.Com...
Hello There, Guest! (LoginRegister) Remember Me? Current time: 07-28-2014, 05:15 AM
   Home  |  About Us  |  Sybase Training  |  Synergy  |  Consulting  |  Job Openings  |  Tech Videos  |  Rules and Disclaimer  |  Search
Post Reply 
Forum Tools
how to separate a string
11-10-2011, 11:51 AM
Post: #1
Quote this message in a reply
how to separate a string


Hi,

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



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



Thanks

Neelima


Find all posts by this user
11-10-2011, 02:40 PM
Post: #2
Quote this message in a reply
RE: how to separate a string


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



BRs,
Pradyut
Find all posts by this user
11-11-2011, 05:06 PM
Post: #3
Quote this message in a reply
RE: how to separate a string


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



Ashirvad to my Shishyas, Cheers to the others
Derek Asirvadem
Information Architect / Sr Sybase DBA
Website
Selection of Useful Documents for the Sybase DBA
Visit this user's website Find all posts by this user
11-14-2011, 03:15 PM
Post: #4
Quote this message in a reply
RE: how to separate a string


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


Find all posts by this user
Post Reply 


Options:
Forum Jump:


Contact Us | SybaseTeam | Disclaimer & Rules | Return to Top | Return to Content | Lite (Archive) Mode | RSS Syndication