x

Wrap a string with Single Quotes

I need to wrap the result of a sql statement in single quotes so that is can be passed as a string later in the sproc....

Each value in the comma separated list - so this result below will reveal 1345 ,5427 ,3166 ,8367 ,7370
- I need to place a Single Quote on either end '1345 ,5427 ,3166 ,8367 ,7370'

    Create table #Customer (
CUST_TYP_ID int, 
CustID CHAR (6))


insert into #Customer values ( 100, '1345')
insert into #Customer values ( 100, '5427') 
insert into #Customer values ( 100, '3166') 
insert into #Customer values ( 100, '8367') 
insert into #Customer values ( 100, '7370') 
insert into #Customer values ( 200, '1257') 
insert into #Customer values ( 200, '1179') 
insert into #Customer values ( 200, '9332') 
insert into #Customer values ( 200, '7372') 
insert into #Customer values ( 200, '2617') 
insert into #Customer values ( 200, '2112') 
insert into #Customer values ( 200, '5827') 



select  (CONVERT(VARCHAR(4000),STUFF((SELECT  ',' +CustID 
       FROM #Customer
       WHERE CUST_TYP_ID = 100
FOR XML PATH('')),1,1,''))) as [Hundred]
more ▼

asked May 11, 2011 at 10:15 AM in Default

siera_gld gravatar image

siera_gld
1k 77 82 84

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you just need a quote on either end then try this:

select
    char(39) + 
    (CONVERT(VARCHAR(4000),STUFF((SELECT  ',' +CustID 
    FROM #Customer
    WHERE CUST_TYP_ID = 100
    FOR XML PATH('')),1,1,''))) + char(39) as [Hundred]

or this:

select
    '''' + 
    (CONVERT(VARCHAR(4000),STUFF((SELECT  ',' +CustID 
    FROM #Customer
    WHERE CUST_TYP_ID = 100
    FOR XML PATH('')),1,1,''))) + '''' as [Hundred]

or use the [quotename][1] specifying a single quote as the character to use:

select
    quotename(
    (CONVERT(VARCHAR(4000),STUFF((SELECT  ',' +CustID 
    FROM #Customer
    WHERE CUST_TYP_ID = 100
    FOR XML PATH('')),1,1,''))), char(39)) as [Hundred]

Oleg

[1]: http://msdn.microsoft.com/en-us/library/ms176114.aspx
more ▼

answered May 11, 2011 at 10:26 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

You can use RTRIM between the two char(39) to remove the space before the final single quote, if your sample result with no space was the absolute requirement.

Building and passing delimited strings around TSQL looks like you could be building something rather complex and dynamic.

You might take another look at your overall approach to see if you can do it a little more straight-up TSQL. You could potentially avoid a performance and troubleshooting quagmire.
May 11, 2011 at 10:33 AM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x33
x20

asked: May 11, 2011 at 10:15 AM

Seen: 2667 times

Last Updated: May 11, 2011 at 10:15 AM