CODEWITHSUNDEEP
sqlfunctionquotename
Matt
Matt

Reputation: 15061

SQL QUOTENAME adding single quotes and a comma

I have a basic table which outputs.

field1
  a
  b
  c

Then i want to add single quotes and a comma so currently i have a simple QUOTENAME.

QUOTENAME(field1,'''')

Which outputs the following results.

field1
  'a'
  'b'
  'c'

But i cant figure out how to get the QUOTENAME to output results like this.

field1
  'a',
  'b',
  'c',

Upvotes: 5

Views: 28124

Answers (3)

PMargarido
PMargarido

Reputation: 41

that doesnt work you dont initialize @MyString so concatenating anything with null will be null

but using this aproach you dont need to initialize it

DECLARE @MyString NVARCHAR(MAX)
SELECT  @MyString = COALESCE(@MyString+ ',', '') + QUOTENAME(field1, '''') 
FROM    YourTable Name

Upvotes: 3

SQLnbe
SQLnbe

Reputation: 868

I know you got your answer but just wanted to make an addition if someone else looks at this and is wondering how to get rid of the last comma after the last value in case they are using the resultset for a dynamic query. 

Declare @MyString nvarchar(max)
select @MyString += QuoteName (field1.'''') + ','
from YourTable Name

Set @MyString = left (@MyString, Len ( @MyString) - 1 )

And to view the results a 

Print @MyString

can be added to evaluate the results.

Hope this helps others looking for this logic when using the quotename and needing that last comma removed :-)

Upvotes: 9

TechDo
TechDo

Reputation: 18649

Try:

QUOTENAME(field1,'''')+','

Upvotes: 16

Related Questions