Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

select with single quotes Expand / Collapse
Author
Message
Posted Monday, March 24, 2014 8:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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
Post #1554265
Posted Monday, March 24, 2014 10:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 3,342, Visits: 7,210
You can concatenate the quotes

SELECT '''' + Column1 + '''' AS quotedColumn1
FROM TableA




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554275
Posted Monday, March 24, 2014 10:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1554282
Posted Monday, March 24, 2014 11:04 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 631, Visits: 1,145
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)';

Post #1554284
Posted Tuesday, March 25, 2014 12:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 1,256, Visits: 3,635
Simple as

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

Post #1554290
Posted Tuesday, March 25, 2014 1:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
ascii value for comma
Post #1554297
Posted Tuesday, March 25, 2014 1:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 1,256, Visits: 3,635
sqlserver12345 (3/25/2014)
ascii value for comma


this time the answer is 44

Post #1554302
Posted Tuesday, March 25, 2014 6:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1554407
Posted Tuesday, March 25, 2014 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
Thanks Jeff/Eirikur .Should have updated the forum.I got what I was looking for.Thanks again.
Post #1554611
Posted Tuesday, March 25, 2014 2:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 1,256, Visits: 3,635
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
Post #1554692
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse