Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: String Concatenation & Local variable

    Guys,

    I have a query, which is used to do simple concatenation.

    create table test1
    (
    ID int,
    Name varchar(25)

    )

    insert into test1
    select 35,'Smith'
    union all
    select 35,'Tape'
    union all
    select 35,'Mark'
    union all
    select 36,'John'
    union all
    Select 36,'Data'


    Query:
    Declare @Name varchar(100)
    Select @Name = @Name +','+ Name
    from test1
    select @Name

    Simply i want to concatenate the name Smith,Tape,Mark,John,Data

    I did for the same one in my query, but i wondered, my query displayed
    ,Data only instead of showing our actual requirement. why ?

    I am using 12.0 version.

    Again i modified my query to

    Declare @Name varchar(100)
    select @Name =' '
    Select @Name = @Name + ',' + Name
    from test1
    select @Name

    But i got the same 'Data' as the output. why ?

    Thanks in advance !

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You start off with
    • @Name = “”
    Then for each record in the table it will do
    • Select @Name = “” + name
    So for the first record you’ll get
    • @Name = “” + “Smith”
    The above results will be discarded when it does the operation again for the second row.

    Eventually it does the last row
    • @Name = “” + “Data”
    and this is the value you’re seeing.

    Rudy pointed out an interesting Sybase function list() (in a similar thread) that could do what you need but I couldn’t get it to work on my version of Sybase 12.5 so I’m not sure it will work for you. You may need to use a loop as in the above thread if list doesn’t work.

    Mike

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    The above my code is working fine in sqlserver.

    so shall we consider it as a drawback of sybase ? or is it rectified in sybase 15.0 version ?

    I don't want to use Loop, i want to acheive it by SET based code. is it possible ?

    what is the internal reason for discarding the result for the second row ?

    List() function - is this available in sybase ASE ? Becuase i have opened that link which you provided, I saw it on the list of ASA function.

    what is mean by ASA ?

  4. #4
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Syntax

    LIST ( string-expression | DISTINCT column-name )

    Parameters
    string-expression

    A string, usually a column name. For each row, the expression's value is added to the comma-separated result.


    DISTINCT column-name

    The name of a column that you are using in the query. For each unique value of that column, the value is added to the comma-separated result.


    Examples
    The following statement returns the value 48 Kennedy Court,54 School Street.


    SELECT LIST( street ) FROM employee
    WHERE emp_fname = 'Thomas'

    Usage
    NULL values are not added to the list. Returns NULL for a group containing no rows.

    Standards and compatibility

    SQL/92

    Vendor extension.


    Sybase

    Not supported in Adaptive Server Enterprise.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •