A simple Excel trick to help with your SQL query

A tedious task

Imagine that you want to write an SQL query to quickly check for something. You will have to use the IN (…) operator in your WHERE clause. However, the items you want to put inside the brackets of your IN (…) operator are lodged comfortably in an Excel column. You could copy and paste those items one by one from the Excel sheet to your SQL tool window. But, just your luck, there are 26 items! And, as far as the SQL dialect you’re using goes, you’ll need to put each item between single quotes (‘…’) and separate them with commas (,). Tedious!

The solution

No worries! I have an easy Excel trick to help you with exactly this kind of trouble!

From all the English alphabets in the left-most column (cell A1:A26), we can transform them into a string ready to be copied and paste in an IN(…) operator inside a WHERE clause of an SQL query (photo by author)

The query:

If you want to check something quickly with the following query:

SELECT *
FROM a_table
WHERE category IN ('a', 'b', 'c',…,'z')

And the list of alphabets in the IN(…) operator can be found in the pale orange column in the photo above. You can copy and paste the alphabets one by one and, in addition, manually add the single quotes and commas. But Excel can also do that quickly for you.

From the photo above, it is obvious that you have to transpose the pale orange column. But that’s not all. Only transposing won’t get you the string completed with single quotes and commas. We can get the exact string in the photo above with the following formula:

=LEFT(CONCAT("'"&TRANSPOSE($A$1:$A$26)&"',"),LEN(CONCAT("'"&TRANSPOSE($A$1:$A$26)&"',"))-1)

The above Excel formula is a combination of three smaller formulas: CONCAT, TRANSPOSE, LEFT, and LEN

Formula explained (from inside-out):

1. We use TRANSPOSE to “transpose” all the alphabet into a row: TRANSPOSE($A$1:$A$26)
2. We then use CONCAT to “concatenate” each alphabets together. We add single quotes and commas at this step by using & to link between these two initial formulas. If we need to have each alphabets inside two single quotes and separated by a comma, we need to add one single quotes before TRANSPOSE and another after TRANSPOSE. The one after TRANSPOSE must also comes with a comma: CONCAT(“‘“&TRANSPOSE($A$1:$A$26)&”’,”)
3. If you are following to the letter up to this part, you’ll notice that we already have the string we want. Almost! I’d say. You’ll notice at this point that we still have a comma dangling at the end of our string. If you perform Ctrl+C and Ctrl+shift+V to get only text and you copy and paste that text inside the IN(…) operator of your SQL script, you need to manually remove this dangling comma, otherwise your SQL script will scream error.
4. It is true that you can easily remove the last comma manually, but if you prefer to get everything out of a single compound formula, the LEFT formula comes in handy here. At this point, the LEFT formula will determine from which string it will start from the left (it’s the string we get from the CONCAT(“‘“&TRANSPOSE($A$1:$A$26)&”’,”) part) and how many characters it will travel from the left on that string. Here, LEN((CONCAT(“‘“&TRANSPOSE($A$1:$A$26)&”’,”))-1 helps us determine this.

What if we don’t need single quotes and our SQL script looks like the following?:

SELECT *
FROM a_table
WHERE category IN (1,2,3,...,26)

Here, it is obvious that our categories are now listed in numbers instead of the English alphabets. Things get even easier now. You can follow all the steps listed above, but you just don’t have to care about the single quote before CONCAT and after CONCAT. But you still need to have the comma.

Provided that the values in the cells A1 to A26 are now numbers 1 to 26, the formula will look like the following:

=LEFT(CONCAT(TRANSPOSE($A$1:$A$26)&","),LEN(CONCAT("'"&TRANSPOSE($A$1:$A$26)&"',"))-1)

The formula is now a bit shorter!

There you go. I hope you learned something from this blog. Until next time. Take care!