Separate values in a query with comma SQL

Question

If I have a query like this:

Select * from customers where partnerID in (1239
2324
323
4435
524
653
778
....)

Is there any shortcut to add commas between the numbers when "space" is used to separate them? I usually use Excel for this to merge the partnerID with "," and then past it back into SQL. I know there is a find and replace function but I don't know how to find the spaces?

I want:

    Select * from customers where partnerID in (1239,
2324,
323,
4435,
524,
653,
778,
....)

SQL Server is a server, it has no UI. Whatever editor you use probably allows multi-line editing. SSMS, Visual Studio Code, Visual Studio and Sublime Text definitely do — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 11, 2020 at 8:58

Wouter · Accepted Answer · 2020-09-11 08:55:34Z

1

In SQL Server Management Studio you can press ALT and hold it when selecting multiple lines with the mouse. Then you can edit all the lines at once. Like this you need to type one comma only.

answered Sep 11, 2020 at 8:55

Wouter

2,9763 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lptr · Accepted Answer · 2020-09-11 10:43:49Z

0

For your query..(if each number is followed by a new line)

in SSMS, Find&Replace (or ctrl+H)

--replace each linebreak with a comma and linebreak

Find what:\n
Replace with:,\n
Find Options: Check Use: Regular Expressions

ref: https://learn.microsoft.com/en-us/sql/ssms/scripting/search-text-with-regular-expressions

answered Sep 11, 2020 at 10:43

lptr

6,8582 gold badges9 silver badges16 bronze badges

Collectives™ on Stack Overflow

Separate values in a query with comma SQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related