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:
- How to Generate Insert Statements
- How to Generate Update Statements
- How to Generate SQL 'IN' Statements
- How to List the Columns of a Table, Quickly!
- Extracting a list of columns from a block of text
- How to Generate Views the easy way
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.
- General help
- Symbols and Keywords
- Built-in Functions
- The 'Where' clause
- Date Time formatting
- Date Time parsing
- Command-Line Automation
- SQL Master Class
- HTML Master Class
- Popular Text Manipulations Made Easy «« bookmark this one
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