ThatJeffSmith

Building your WHERE IN () Lists

Have you ever needed to write a query and filter your results with a WHERE EXISTS or a WHERE IN list of values? Your list will be comma delimited, and you’ll need to quote the strings appropriately. Not so bad if you have a few values, but what if you have 42 or 420 or more? You are looking at the data in Toad, and you want to just WISH it into a a comma-delimited list (and maybe quoted), to feed into your

 SELECT ...
 FROM X
 WHERE Y IN (
  'a',
  'b',
  'c',
  'd',
  ...,
  'n'
);

Surely there is a way to have Toad build this list for you? The answer is ‘YES’ – and you don’t even need to learn Regular Expressions (REGEX – wiki). As an aside, I do recommend you learn REGEX, it is definitely worth the investment of your time.

Let’s Do It!

Step One: Get your data – in other words, write your query or filter your table until you get the list of values you want to send to your query.

Step Two: Trim the extra columns

Remove the extraneous information from your grid.

Step Three: Export Data – You will want to use Delimited Text to Clipboard. Choose ‘comma’ for your delimiter. Do not include column headers. Check “Include delimiter after last column.” Click OK.

Does this look familiar?

Step Four: Paste the text into your () and remove the last trailing COMMA. Presto-changeO, we just saved you a lot of tedious copy and paste work.


This became our theme song while doing a sales tour in Albany, NY. I’ll smile and think of Paul every time I hear that intro bass/rhythm intro :)