SQL Server Place quotes & add comma around many items in WHERE

wqlqzqxt 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(94)

I have a very long list of codes for a WHERE statement in SQL Server that I would like to 'quickly' add quotes around each one and a comma at end of each one.

I would like to take the huge list like this (with 100's of them in my WHERE)

WHERE CODE IN 
(P279273
Q2793567
Q29262718)

and 'quickly' insert quotes and commas like this (doing it directly in SQL)

WHERE CODE IN
('P279273',
'Q2793567',
'Q29262718')

Anyone know how to insert those in quickly in SQL Server directly without manually going line by line (and not doing it in Excel first)?

sql-server

来源：https://stackoverflow.com/questions/77108567/place-quotes-add-comma-around-many-items-in-where

2条答案

按热度按时间

ig9co6j11#

Just another option

Example

Declare @S varchar(max) = 'P279273
Q2793567
Q29262718
Q99999
Q00000
'

Select string_agg(v,',')
 From (
        Select v=concat('''',replace(value,char(13),''),'''')
         From string_split(@S,char(10))
         where value<>''
      ) A

Results

'P279273','Q2793567','Q29262718','Q99999','Q00000'

赞(0）回复(0）举报 2023-10-15

qnzebej02#

First get a line-separated list of your elements.

If you use visual studio code/azure data studio, you can use Ctrl + Alt ( Shift + Alt for Visual Studio/SSMS) and down arrow to make multiple cursors for each line.
Once you see a cursor for each line, insert your single quote.
Hold Ctrl and press the right arrow (or End if one or more lines has spaces in the words) to move to the right side of the keyword. Insert another single quote and a comma.
Delete the comma on the last line. Cut and paste the list into your WHERE IN ()

Article on this - https://www.sqlservercentral.com/blogs/quick-tip-quickly-bulk-edit-lines-in-ssms

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Place quotes & add comma around many items in WHERE

2条答案

相关问题

热门标签

最新问答