Wrap a string with Single Quotes

Question

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]

Answer 1

0

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 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

answered May 11 '11 at 10:26 AM

Oleg
15.4k ● 1 ● 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 '11 at 10:33 AM KenJ

(comments are locked)

Wrap a string with Single Quotes

Follow this question

Related Questions