How to use the column to sql in list builder
- Paste the column of values, one per line. A column copied from a spreadsheet works as it is.
- Choose how to quote: Auto detect writes numbers bare and everything else in single quotes, Always text quotes everything and Numbers only refuses anything that is not a number.
- Optionally enter a column name to get a ready clause such as customer_ref IN (...), and a number of values per list to split a long list.
- Read the SQL in the table and use Copy result to copy the count and the lists. The page also reports blank lines skipped and repeated values removed.
Formula
text value: 'value' with every single quote inside doubled
number: written as it is (for example -7.5 or 1e3)
word NULL (any capitals): NULL, when that box is ticked
lists of up to N values joined by commas, and several lists joined by OR
A value is treated as a number when it matches an optional sign, digits with an optional decimal part and an optional exponent. Anything else, such as 007x or a date, is quoted.
Eight lines in, six values out
The sample column has eight lines: A-1001, A-1002, A-1003, O'Brien, A-1002 again, a blank line, NULL and B-2001. With repeats removed and the word NULL written as NULL, the page returns customer_ref IN ('A-1001', 'A-1002', 'A-1003', 'O''Brien', NULL, 'B-2001'), which is six values. One repeat was removed and one blank line skipped.
Seven numbers in lists of three give (101, 102, 103), (104, 105, 106) and (107), to be joined with OR. Numbers-only mode on the sample would stop at the first value that is not a number and name it.
When a long list is a problem
Some databases limit the number of values in an IN list, or the length of a statement. Splitting the values into lists of a set size keeps each list within the limit. Join the lists with OR so the clause still tests every value. For very large sets of values, a temporary table or a join is usually faster and clearer than a huge list.
Quoting and safety
Doubling single quotes is the standard way to put a quote inside an SQL string, and it is enough for ordinary values. It is not a defense against injection when values come from users, because other characters and database settings can matter. In code, send values as bound parameters instead of building text. Some databases, such as MySQL in certain modes, treat the backslash as an escape character, so check how yours treats it.
To build INSERT statements from a table, use the CSV to SQL Generator. To tidy a finished query, use the SQL Formatter, and to clean the column first, the Text Cleanup tool.
Assumptions and limits
- One value per line. A value cannot contain a line break.
- The page does not check the values against a database or a column type.
- Number detection is by text pattern, so leading zeros are kept (007 is written as 007, a number, not as '007').
- Up to 20,000 lines are accepted.
- The values stay in this browser.
Frequently asked questions
Why is a value with a leading zero written without quotes?
In Auto detect mode anything made only of digits is treated as a number, so 007 comes out as 007. If it is a code, choose Always text to keep it as a string.
How do I filter on several lists?
Use a number of values per list and join the results with OR, as in column IN (list one) OR column IN (list two). The page notes how many lists it made.
What does the NULL option do?
With it on, a line that says NULL in any capitals is written as the SQL keyword NULL. Note that column IN (NULL) never matches anything, because comparing with NULL is never true in SQL, so test for NULL separately with IS NULL.
Is this safe against SQL injection?
Doubling quotes handles ordinary strings, but it is not a complete defense. When the values come from users, use bound parameters in your code.