What actually has to happen to each value
Three things, in order. Split the paste into values, which is where a trailing comma or a stray blank line quietly adds an empty entry. Escape each value for a string literal, which in standard SQL means doubling every single quote inside it, so O'Brien becomes 'O''Brien'. Then wrap and join, and check the count against whatever ceiling your database has.
Each step has a failure mode you will recognise. A missed empty entry gives you IN ('a', '', 'b'), which matches nothing but does not error. A missed apostrophe gives you a syntax error if you are lucky and a truncated string if you are not. A list that exceeds the ceiling gives you an error message that names a limit you have never heard of, usually while you are in a hurry.
Why the list gets chunked
There is no universal maximum for an IN list, which is exactly what makes this annoying. Some database engines document a hard item count. Others have no count limit but impose a maximum statement length, or a maximum number of bind parameters, and you meet whichever comes first. Drivers and connection poolers add their own limits on top. The practical effect is the same: past a certain size the statement stops working, and the number varies by where you are running it.
Splitting into chunks joined with OR sidesteps all of it, because a chain of separate IN lists is ordinary SQL that every engine accepts. The default chunk size of a thousand is a conservative choice that sits under the strictest common limit. Lower it if you are hitting a statement length ceiling; raise it if you know your engine is comfortable.
NULL does not belong in an IN list
This one catches experienced people. Comparing anything to NULL produces unknown rather than true or false, and x IN (1, 2, NULL) is shorthand for a chain of equality comparisons, so the NULL branch can never make the whole expression true. A row where the column is NULL will not be returned no matter what is in the list. Worse, x NOT IN (1, 2, NULL) can never be true either, which silently returns nothing at all and is one of the more baffling ways to lose a result set.
If you want rows where the column is empty, say so separately: col IN ('a','b') OR col IS NULL. Values that read as the word null are flagged in the results for exactly this reason, since a spreadsheet export full of the text NULL is a common source.
Escaping is not the same as safety
Doubling apostrophes produces a correct literal. It does not make a query built by concatenating strings safe, and the distinction is worth being pedantic about. In application code the answer is a parameter placeholder and a bound value, every time, with no exceptions worth arguing over. The driver then handles the encoding, the value is never parsed as SQL, and there is nothing to get wrong.
Escaping rules are not even uniform. Standard SQL uses doubled quotes and nothing else, but some engines and configurations also treat a backslash as an escape character, which means a value ending in a backslash can escape the closing quote and change the meaning of everything after it. This page implements the standard doubling. Use the output for ad hoc queries you run yourself against data you trust, not as a component of an application.
| Input value | Quoted output |
|---|---|
ORD-1001 | 'ORD-1001' |
O'Brien | 'O''Brien' |
'already quoted' | 'already quoted' — outer quotes stripped first |
| empty line | dropped, or '' if you turn that off |
NULL | 'NULL' as text, and flagged |
When to stop building lists
A few hundred values is a reasonable thing to paste into a query. A few thousand is a signal that the shape of the work has changed. At that size, the better move is to load the values into a temporary table and join against it: the plan is usually far better, the statement stays short, the values can be indexed, and you can reuse them across several queries instead of pasting the same wall of text repeatedly. The VALUES output on this page is built for that — it produces the rows to insert.
The other signal is repetition. If you are building the same list weekly from the same export, the list belongs in a table that gets refreshed, not in your clipboard. Once the query is settled, the SQL formatter will lay it out readably, and if the values arrived as a spreadsheet the CSV converter and the list compare handle the steps before this one.
Questions people ask
Why does my apostrophe appear twice in the output?
Because that is how a single quote is written inside a SQL string literal. The value O apostrophe Brien becomes the literal with two apostrophes in the middle, and the database reads it back as one. If you see doubled quotes in a query result rather than in the query, something has escaped the value twice — usually an application layer escaping before handing the string to a driver that escapes again.
What chunk size should I use?
A thousand is a safe default because it sits under the strictest common item limit. If you get an error about statement length rather than item count, reduce it, since long values reach a length ceiling well before they reach a count ceiling. If you know your engine has no fixed item limit and your values are short, several thousand per chunk will work. There is no single right answer, which is why it is an input rather than a constant.
Can I use the output in application code?
You should not. Building SQL by concatenating escaped values is the pattern that parameter binding exists to replace, and it fails in ways that are hard to spot — a different escape configuration, a value from an unexpected source, a code path that skips the escaping. Use placeholders and bound parameters. This page is for queries you write and run yourself against data you trust.
It quoted my numbers. Why?
Automatic quoting only leaves values unquoted when every one of them looks like a plain number. One value with a letter, a leading zero you wanted to keep, a currency symbol or a stray space in it, and the whole list is quoted as text, because a half-quoted list is a syntax error. Set the quoting option to numbers explicitly if you know the values are numeric, and read the warning it produces if any of them are not.
Does it handle values pasted from a spreadsheet?
Yes. A column copied from a spreadsheet arrives as one value per line, which is the default when a newline is present. If you copied a row rather than a column you will get tabs, which are detected too. Values that arrive already wrapped in quotes have the outer quotes stripped before requoting, so you do not end up with doubled quotation marks in the literal.