Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 10, 2016 10:58 PM
Points: 324, Visits: 1,177
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:05 PM
Points: 7,721, Visits: 16,672
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
Post #1554275
Posted Monday, March 24, 2014 10:51 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 41,257, Visits: 38,569
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."

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:50 AM
Points: 1,336, Visits: 2,523
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
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:30 PM
Points: 5,852, Visits: 15,652
Simple as

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

Post #1554290
Posted Tuesday, March 25, 2014 1:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 10, 2016 10:58 PM
Points: 324, Visits: 1,177
ascii value for comma
Post #1554297
Posted Tuesday, March 25, 2014 1:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:30 PM
Points: 5,852, Visits: 15,652
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 41,257, Visits: 38,569
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."

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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 10, 2016 10:58 PM
Points: 324, Visits: 1,177
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
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:30 PM
Points: 5,852, Visits: 15,652
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