Mixed numeric and text values
Input
1
2
abcOutput
id IN (1, 2, 'abc')Numbers stay unquoted; the text value is quoted.
Ctrl+↵
Input
TEXT input
Output
ResultOptions
Optional.
About this tool
Turning a column of IDs from a spreadsheet or a log into a SQL IN clause is tedious by hand and easy to get wrong. Paste the values one per line or comma-separated, and you get a correctly quoted clause.
Quoting is decided per value: numbers are left bare and anything else is quoted with its apostrophes doubled, which is what makes the result safe to paste into a query.
How to use it
- Paste or upload your textDrop a file onto the input pane, use the file picker, or paste the text directly.
- Adjust the options if neededThe defaults suit most input; open Options to change the behaviour.
- GeneratePress Generate, or use Ctrl+Enter (Cmd+Enter on macOS).
- Copy or downloadCopy the result, or download it as a .sql file.
Worked examples
Each example below is executed against this tool by the test suite, so what you see is what the tool actually produces.
What to watch for
The details that decide whether a conversion is correct, and where information can be lost without any error being raised.
- Quoting is decided per value
- In automatic mode a purely numeric value is written unquoted and everything else is quoted. That matters because quoting a number can prevent index use in some databases, while failing to quote a string is a syntax error. Force quoting when a column is text but holds numeric-looking values, such as a zero-padded reference code.
- Line wrapping for long lists
- Values are wrapped across lines in groups so the clause stays readable in a query editor. This is cosmetic only, SQL ignores the whitespace.
- Large IN lists perform badly
- Oracle rejects more than 1,000 items in a single IN list outright, and other databases slow down well before that as the parser and planner handle each value. Past a few hundred values, a temporary table joined to your query is faster and avoids the limit. The tool warns when a list gets large.
- Duplicates are kept
- Repeated values are preserved and counted in a warning. They do not change results, since IN is a set membership test, but they do make the query longer and often indicate a problem in the source data.
- NULL never matches in an IN list
- This is a genuine SQL trap worth knowing. Because NULL comparisons yield unknown rather than true, a NULL in an IN list matches nothing, and a NOT IN list containing NULL returns no rows at all. Use IS NULL separately for null checks.
Limitations
- Produces the clause only, not a complete query.
- Cannot know the column type, so quoting is inferred from each value.
- Processing happens in your browser, so very large inputs are bounded by available memory. Files above roughly 10 MB are handled but will feel slower, and multi-hundred-megabyte files are better suited to a command-line tool.
Questions
- How many values can I include?
- Oracle caps IN at 1,000 items, and other databases degrade as the list grows. Beyond a few hundred, join against a temporary table instead.
- Why are some values quoted and others not?
- Numeric values are left bare because quoting a number can prevent index use; everything else is quoted. Force quoting if the column is text but the values look numeric.
- Why does NOT IN with a NULL return nothing?
- Comparing anything to NULL yields unknown rather than false, so NOT IN can never be satisfied when the list contains NULL. Handle nulls with a separate IS NULL condition.