select with single quotes

  • I have a table A

    with 1 column

    Table A

    ColA

    121

    3423

    4564

    5435

    63

    76

    8989

    How can I write a select query which will output the results in this format

    ColA

    '121',

    '3423',

    '4564',

    '5435',

    '63',

    '76',

    '8989',

    Thanks

  • You can concatenate the quotes

    SELECT '''' + Column1 + '''' AS quotedColumn1

    FROM TableA

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • If the content of the column is less than 128 characters, the you could also do the following...

    SELECT QUOTENAME(Column1,'''') AS quotedColumn1

    FROM TableA;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/24/2014)


    If the content of the column is less than 128 characters, the you could also do the following...

    SELECT QUOTENAME(Column1,'''') AS quotedColumn1

    FROM TableA;

    if its more then 128 characters, then you need a UDF something like this

    declare @x nvarchar(max), @y nvarchar(max), @z nvarchar(max), @QT nchar(1);

    set @QT = '"';

    set @x = replicate('XYZ'+@qt,33);

    set @y = quotename(@x,@qt);

    set @z = @QT+replace(@x,@qt,@qt+@qt)+@qt;

    select @x as '@x', len(@x) as 'len(@x)';

    select @y as '@y', len(@y) as 'len(@y)';

    select @z as '@z', len(@z) as 'len(@z)';

  • Simple as

    select char(39) + column + char(39)

  • ascii value for comma

  • sqlserver12345 (3/25/2014)


    ascii value for comma

    this time the answer is 44 😎

  • Eirikur Eiriksson (3/25/2014)


    sqlserver12345 (3/25/2014)


    ascii value for comma

    this time the answer is 44 😎

    Heh.... I caught the reference. Too funny! 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff/Eirikur .Should have updated the forum.I got what I was looking for.Thanks again.

  • Jeff Moden (3/25/2014)


    Eirikur Eiriksson (3/25/2014)


    sqlserver12345 (3/25/2014)


    ascii value for comma

    this time the answer is 44 😎

    Heh.... I caught the reference. Too funny! 😛

    Not to mention all the fish 😀

  • sqlserver12345 (3/25/2014)


    Thanks Jeff/Eirikur .Should have updated the forum.I got what I was looking for.Thanks again.

    What did you end up with finally?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/25/2014)


    sqlserver12345 (3/25/2014)


    Thanks Jeff/Eirikur .Should have updated the forum.I got what I was looking for.Thanks again.

    What did you end up with finally?

    Making way for the new CONCAT function in 2012/2014, it would be

    SELECT CONCAT(CHAR(39),[COLUMN],CHAR(39),CHAR(44))

    😎

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply