Building an IN list using REPLACE

 

Have you ever had the need to build an IN list from some rows you had in excel? Or maybe you have a bunch of data you pulled out of a results set from some other server somewhere and you are unable to join tables so you need to plug the values into an IN clause?

Well I have come across this a few times and I have always either encapsulated every value manually in Apostrophes and delimited with a comma or used a method in Excel.

Its a bit of a silly one really but its kinda neat too, lets let SQL do do the work for us:

Here is a bunch of Tabular data:

 

 

 

I want to copy the contents of these rows and encapsulate each value with Apostrophes and then delimit with a comma so that I can place this directly into my IN clause, but remember this is one single string not a column where we can apply something per row so the likes of XML PATH is no use here.

So here is one way to do this simply with SQL:

Sample Data:

Danish
German
Greek
English
Finnish
French

 

I need to pass this into a SELECT so I add an apostrophe at the beginning and end like this:

‘Danish
German
Greek
English
Finnish
French’

then add my SELECT:

SELECT
'Danish
German
Greek
English
Finnish
French'

So far the results look like this:

 

 

Then use REPLACE to first replace the CHAR(13)+CHAR(10) with an Apostrophe, Comma then another Apostrophe:

 

SELECT REPLACE(
'Danish
German
Greek
English
Finnish
French'
,CHAR(13)+CHAR(10),''',''')

It now looks like this:

 

 

So now its missing a beginning Apostrophe and an end apostrophe, so we can just simply add this to the Query as below:

 

SELECT ''''+REPLACE(
'Danish
German
Greek
English
Finnish
French'
,CHAR(13)+CHAR(10),''',''')+''''

 

 

Great so now we have what we were after… BUT when we copy and paste it’s all on one row:

--RESULT:
'Danish','German','Greek','English','Finnish','French'

 

Perhaps we want it back to the form it was originally in, tabular form?

 

We can alter this slightly:

 

SELECT ''''+REPLACE(
'Danish
German
Greek
English
Finnish
French'
,CHAR(13)+CHAR(10),''','+CHAR(13)+'''')+''''

--RESULT:
'Danish',
'German',
'Greek',
'English',
'Finnish',
'French'

 

If you really wanted to you could create a Stored procedure out of it, for example:

 

CREATE PROCEDURE sp_BuildINList
(
@String VARCHAR(MAX)
)
AS
SELECT ''''+REPLACE(
@String
,CHAR(13)+CHAR(10),''','+CHAR(13)+'''')+'''' AS IN_List

 

--Example execution:

EXEC sp_BuildINList
'Danish
German
Greek
English
Finnish
French'

 

A neat little trick, I am sure there are other ways of doing this – I used to use Excel but if I can keep things in SQL then I only ever need one application 🙂

 

Thanks for reading.