Search code examples
sql-serverinsertwhere-in

Place quotes & add comma around many items in WHERE

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

Solution

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