How to enclose a list of values into single quotes for SQL query? Microsoft Excel Example

Many times you get a list of values that 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 like VARCHAR or CHAR because VARCHAR values need to be enclosed in the single quotes in most of the database like SQL Server, Oracle, or MySQL. If a number of values are less than 5 then it makes sense to add single quotes around them and separate them by comma manually so that you can use them on IN clause of your SQL query, but if it's more than 5 like 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 teams 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 to this problem using Microsoft Excel, a tool that many of us know but don't use effectively. By using Microsoft excel you can just write a formula to concatenate values from one column with single quotes and commas. Later you can use 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 - Example

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

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

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:

Step 4:
Now, column B has values that 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 queries.

I am not a Microsoft Excel expert but I have seen people using Excel to solve different types of problems like 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.

Good knowledge of Microsoft Excel will seriously help you in your day-to-day job whether you are a developer, support professional, project manager, or any technical user. I am working on compiling some useful Excel tips for Java developers like this one. If you have any, then please share with us. You can also see these best Microsoft Excel courses to learn more about how to use Excel better and more productivity.