Building an IN list using REPLACE

2017-08-18 14_28_59-Presentation1 - PowerPoint

 

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:

 

2017-08-18 14_14_42-Book1 - Excel

 

 

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:

 

Step1

 

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:

 

Step2

 

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),''',''')+''''

 

Step3

 

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.

2 thoughts on “Building an IN list using REPLACE

Add yours

  1. I personally find it easy to just paste into SSMS and do a find replace with Regex. Steps would be paste from Excel, Ctrl+H, in the search enter \r\n, in the replace enter ‘,’ Check the regex button and hit Alt-A. Then you just need to cleanup the start and end.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: