SQL Tricks: the IN Clause...

Read about an interesting way to use MS Word to create part of a SQL statement...

Date: November 21, 2005

Recently I had a large amount of data for an in clause in a SQL string. Rather than formatting each item to appropriately meet the SQL parser syntax, I used Microsoft Word instead. Microsoft Word has many functions that can assist a developer or analyst with data queries or data manipulation. Other than some of the more basic features, such as find/replace word(s) and word count, we are going to use a more complex feature of the find/replace tool.

Microsoft Word supports searching for more than just normal characters on the keyboard; instead, it also includes support special characters, such as new line and tab characters. Using this approach, data that is in a vertical format (separated by new lines) can be replaced with something closer to the final "product." Let's take a look at the data. This data could be typed in, come from a text file, or be from an excel file. If from Excel, paste as unformatted text so the table doesn't come over as well. This data is a small subset of data that could be much larger in nature.

Alabama Alaska California Louisiana Maryland Pennsylvania Tennessee Texas

Word can find newline characters with the "^p" character. To essentially get this in the appropriate format, the find/replace will find all ^p characters and replace it with ', ' string. What happens is that there is a newline character after every item above. So after Alabama, Alaska, etc, the last character is a newline character. Replacing a newline with ', ' will replace the newline character with the string, and will format the results as such:

Alabama', 'Alaska', 'California', 'Louisiana', 'Maryland', 'Pennsylvania', 'Tennessee', 'Texas', '

This is close to the final format for an in clause in a SQL string. Remember, an in clause looks like this: in ('item', 'item') All that is left is to add this to the beginning:

('Alabama

For the end, remove the last , ' text and add a ), which will look like:

Texas')

Our final text will be:

('Alabama', 'Alaska', 'California', 'Louisiana', 'Maryland', 'Pennsylvania', 'Tennessee', 'Texas')

The problem with the way Microsoft does quotes is it uses a different quote character that most SQL editors don’t recognize, but it is easily changeable through a find/replace mechanism in the SQL editor tool. To finish this example off, add the query before it, and you have your large amount of data formatted correctly with minimal effort:

SElect * FROM Customers WHERE State IN ('Alabama', 'Alaska', 'California', 'Louisiana', 'Maryland', 'Pennsylvania', 'Tennessee', 'Texas')