NimbleText

Manipulate text and data with light-weight patterns

How to Generate SQL 'IN' Statements

We've all been there.

You have a list, be it a short list or a long list, and you need to turn it into a SQL 'in' statement.

Let's say we've got this data:

First Name, Last Name, Company
Walter, Greer, Aratos Pty Ltd
Conrad, Dunbar, Lupus Tech
Mandy, Miron, Horse Lane Pottery

...and we want to write a query like this:

Select * from Contacts where CompanyName in (
'Walter',
'Conrad',
'Mandy')

We figure we could solve it with a regular expression -- but our mind recoils in horror at the suggestion. So we take the dumb way out and reformat it by hand.

I'm telling you now -- there is a simpler solution. NimbleText eats data like this and spits out 'IN' statements for breakfast!

This super-simple pattern will pretty much do the job for us:

try it → 
'$0',

It's dollar-zero (meaning, 'the zeroth column'), surrounded by quotes and followed by a comma. That pattern gets repeated for every row in our data.

...giving us a result like this:

'First Name',
'Walter',
'Conrad',
'Mandy',

...that we can pretty much copy and paste into our query of choice.

But we can do a little better than that!

For extra credit I'll show you how to skip the header row, and drop that trailing comma.

The '$each' token means 'Repeat the following bit for each row of our raw data. And the more specific '$each+' (each PLUS) token means "skip the header row, and then repeat this pattern for each data row.

So we can use this pattern to skip the header row:

try it → 
$each+
'$0',

...which gives this result:

'Walter',
'Conrad',
'Mandy',

Hmm, we still have our trailing comma. We can get rid of that using this common NimbleText snippet (available from the snippet menu)

try it → 
<% if ($rownumOne != $numrows) {','} %>

Now we're going to combine that with a little logic to output the beginning and trailing parentheses.

And there we have it, a reusable 'in' statement generator, and all the wisdom you need to build it yourself.

But in reality it's much simpler than that!

As a matter of fact, this pattern is one that you need never type out by hand. It's such a common problem, that this pattern is included in the snippet menu of nimbleText. Under 'snippets' just select "SQL: 'in' statement" and it will put this pattern straight into the pattern box for you:

try it → 
$once
in 
($each'$0'<% if ($rownumOne != $numrows) { ','; } else {')'} %>

And this is just one very simple example of a SQL query that NimbleText will help you generate.

We've also written guides on many other ad-hoc queries you can generate:

Further help

You can also get general help, help on all the symbols and keywords, or on the built-in functions, filtering with a where clause, help with the powerful command-line automation, or applying custom formats to your dates and times.

You need to purchase a license to unlock all the features in NimbleText.

If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.

Download NimbleText