Saturday, March 18, 2017

How to enclose a list of values into single quotes for SQL query - Use Microsoft Excel

Many times you get a list of values which you want to check in the database to confirm if they exist in your tables or to get more information bout them. For example, you got a list of 100 stocks and you want to check their last day closing prices in the database. The problem arises when values to be searched are Strings e.g. VARCHAR or CHAR because VARCHAR values need to enclosed in the single quotes in most of the database e.g. SQL Server, Oracle or MySQL. If number of values are less than 5 then it make sense to add single quotes around them and separate them by comma manually so that you can use then on IN clause of your SQL query, but if it's more than 5 e.g. 50 or 100 then manually enclosing them into single quotes will take a lot of time and most importantly programmers are not supposed to do such silly stuff manually.

I did it a couple of team before realizing it's too painful and the situation is too frequent. I first tried to use a text editor like Wordpad and Notepad to convert a list of values into something I can use in the IN clause of SQL. I tried regular expression and macros but it wasn't too easy as many text editors don't support find/replace on a regular expression.

I eventually find a simpler solution of this problem using Microsoft Excel, a tool which many of us know but doesn't use effectively. By using Microsoft excel you can just write a formula to concatenate values from one column with single quotes and comma. Later you can use the Excel's fill technique to copy it overall cell values and you are done. Here is the example and formula to do so:

Steps to enclose a list of String into single quotes using Microsoft Excel

Step 1:
Copy the list of values into the first column (A) in your Excel Work Sheet

how to add single quotes in excel on each values

Step 2:
Copy the following formula into second column (B) on the first cell
=CHAR(39) & A1 & CHAR(39) & CHAR(44)

how to add single quote and comma after each record in excel

Here 39 is the ASCII value of single quote (') and  44 is the ASCII value of the comma.

Step 3:
Drag this formula to the remaining cell as shown below:

How to enclose list of values into single quotes Excel

Step 4:
Now column B has values which are enclosed in single quotes and separated by a comma, you can directly use them in the SQL query as shown below:

SELECT ClosingPrice from StockDetails where
Company Name IN
'Ambalal Sarabhai Enterprises Ltd',
'Assambrook Ltd',
'PH Capital Ltd',
'Mavi Industries Ltd',
'Oswal Spinning & Weaving Mills Ltd',
'TPI India Ltd',
'Zuari Industries Ltd',
'Oscar Investments Ltd',
'Maestros Mediline Systems Ltd',
'Mediaone Global Entertainment Ltd',
'GEE Ltd',
'Kaycee Industries Ltd',
'Rathi Steel & Power Ltd',
'Clutch Auto Ltd'

Just remember to remove the comma from the last value before you copy them into SQL editor.

This nicely solves the problem of converting a list of values into single quote enclosed and comma separated values which can be used with SQL query.

I am not a Microsoft Excel expert but I have seen people using Excel to solve different types of problems e.g. some time you get two lists of values and you have to find out which values are common and which values of one list doesn't exist on other, for this you can use the VLOOKUP function of Microsoft Excel.

A good knowledge of Microsoft Excel will seriously help you in your day-to-day job whether you are a developer, support professional, project managers or any technical user. I am working on to compile some useful Excel tips for Java developers like this one. If you have any, then please share with us.

Further Learning
Introduction to SQL by Jon Flanders
Introduction to SQL Server
Head First SQL


Anonymous said...

You can also use Notepad++ record and play option.

Andreas said...

This is something you'd do easier with a macro in Notepad++ or Sublime.

jai said...

Another way:
1st Cell Formula: "'" & A1 & "'"
2nd Cell Formula: A2 & ", '" & B1 & "'"
Now drag the 2nd cell formula to the end. Take the last cell of col B which will contain the combined string of each value from col A within single quote, separated by comma.

Post a Comment