question

sindhu avatar image
sindhu asked

How to add single quotes in stuff for all values

declare @columnlist1 nvarchar(max) = stuff((select ',' + ''''+ cast(s.name as varchar(max)) + '''' from sys.columns s where s.object_id = object_id('ws_user_flat_t_test264') and s.name not in ('id') order by column_id for xml path('')), 1, 2, '') print @columnlist1 the o/p is as below: includeAnniversaryInTicker','includeBirthdayInTicker','includeReceivedAwardsInTicker','Service_Date' I need single quote for first value too like below.How to achieve this? 'includeAnniversaryInTicker','includeBirthdayInTicker','includeReceivedAwardsInTicker','Service_Date'
sql-server-2008sql-server-2008-r2tsql
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@sindhu Your script is almost correct, but you accidentally force the very first single quote to be removed because you use stuff(Input, 1, 2, '') so it removes the very first single quote along with the leading comma. The third parameter for stuff in your case should be 1, not 2, i.e. declare @columnlist1 nvarchar(max) = stuff(( select ',' + ''''+ cast(s.[name] as varchar(max)) + '''' from sys.columns s where s.[object_id] = object_id('ws_user_flat_t_test264') and s.name 'id' order by column_id for xml path('')), 1, 1, '') print @columnlist1
0 Likes 0 ·
Ohh, Got it Thank You. Meanwhile I found a workaround of removing stuff and inserting into temporary table and did it.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.