Post reply

select with single quotes

  • March 24, 2014 at 8:25 pm

    #151332

    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

  • March 24, 2014 at 10:06 pm

    #1699721

    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

  • March 24, 2014 at 10:51 pm

    #1699726

    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)

  • March 24, 2014 at 11:04 pm

    #1699728

    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)';

  • March 25, 2014 at 12:47 am

    #1699730

    Simple as

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

  • March 25, 2014 at 1:11 am

    #1699736

    ascii value for comma

  • March 25, 2014 at 1:29 am

    #1699741

    sqlserver12345 (3/25/2014)

    ascii value for comma

    this time the answer is 44 😎

  • March 25, 2014 at 6:06 am

    #1699819

    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)

  • March 25, 2014 at 11:40 am

    #1700001

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

  • March 25, 2014 at 2:58 pm

    #1700072

    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 😀

  • March 25, 2014 at 5:10 pm

    #1700098

    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)

  • March 26, 2014 at 12:37 am

    #1700143

    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