How to build a SQL IN list from pasted values

Free toolThis tutorial uses the SQL IN List Builder. It runs in your browser, so open it in another tab and follow along.

An IN list is a list of SQL literals inside parentheses. You write it when a column has to match one of several values.

SELECT id, title FROM posts WHERE status IN ('published', 'draft');

The list is the part that is annoying to type by hand. Each text value needs quotes. A quote inside a value has to be doubled, or the string ends early and the rest of the line is a syntax error. A blank line, a comma inside a city name, and the word NULL each need a decision. The SQL IN list builder takes one value per line and writes that list.

It writes literals. It does not write placeholders. If the values come from a person, a parameter is the right tool and this page is the wrong one. More on that at the end.

Three statuses, one of them with a quote

Paste these three lines. The sample on the page is this text, including the trailing newline:

published
draft
O'Brien

The output is:

Values: 3
IN ('published', 'draft', 'O''Brien')

Three values, so the first line says Values: 3. The SQL line is the part you copy. Copy on the page skips the count and copies the SQL only.

published and draft are wrapped in single quotes. O'Brien has a quote in the middle. Standard SQL escapes a quote by writing it twice, so the value becomes 'O''Brien'. The database reads that as seven characters: O, the apostrophe, B, r, i, e, n. It does not see the end of the string at the apostrophe.

There is no note on this one. The list is the ordinary case. A trailing newline after the last value is not a fourth value. The page drops that empty segment so a normal paste from an editor does not grow a blank.

Numbers, NULL, and a leading zero

Switch quoting from “Quote every value” to “Leave numbers and NULL bare”. Set the column to status. Paste:

1
2
007
NULL

Values: 4
status IN (1, 2, '007', NULL)

1 and 2 match a plain integer, so they are bare. The database sees numbers. NULL is the SQL null, not the four letters N-U-L-L, so it is bare too. 007 looks numeric and is not. Unquoted, 007 is the number 7. The page quotes it so the text stays 007.

Two notes come with that output.

A value with a leading zero is quoted. Unquoted, 007 is the number 7.

NULL is unquoted, so it is the SQL null, not the word NULL.

The column is checked before any of that. status is an identifier. posts.status is an identifier with one dot. A name with a space, a quote, or a second dot is refused: The column name has to be an identifier like status or posts.status.

Leave the column empty and the SQL starts at IN. You can paste it after a column you already typed.

The integer rule is strict on purpose. 1 is a number. 01 is not, because of the leading zero. 1.50 is a decimal, so auto mode leaves it bare. 1e2 is not, because this page does not treat exponent notation as a number. +1 is not. -2 is, and so is -2.5. Anything else is quoted.

A comma is part of the value

Paste one line, Rome, Italy.

Values: 1
IN ('Rome, Italy')

A comma is part of the value. This page splits on lines, not commas.

That note is the rule. A CSV row would split Rome, Italy into two columns. This page does not. If you paste a comma-separated line and expect several values, you get one value with commas in it. Put each value on its own line.

The other direction matters too. A value that contains a newline cannot be pasted as one value, because the newline is the separator. Replace the line break with a space before you paste, or keep the values in a file and generate the list from code.

Blank lines

Paste published, then an empty line, then draft.

Values: 2
IN ('published', 'draft')

Blank lines were skipped.

The empty line is not an empty string. It is a gap in the paste. The page drops it and says so.

Turn on “Keep blank lines as empty strings” and paste published plus a blank line.

Values: 2
IN ('published', '')

Now the blank is '', a real empty string. Use that when an empty string is a value you want to match. Leave it off when the blank line is just how the paste was formatted. A file that ends with a newline still does not add an extra empty string. Only a blank line between values, or a blank line you kept on purpose, becomes ''.

A backslash is copied through

Paste C:\Users\flavio.

Values: 1
IN ('C:\Users\flavio')

A backslash is left as-is. Standard SQL does not treat it as an escape, and neither does Postgres with standard_conforming_strings on.

The SQL text contains one backslash, the same one you pasted. Standard SQL has one string escape, and it is the doubled quote. A backslash is data. Postgres agrees when standard_conforming_strings is on, which has been the default for a long time. MySQL in a mode that still treats \' as an escape is the odd one out. This page does not emit MySQL backslash escapes. If you need those, you are in a different dialect and you should not paste this list in unchanged.

What the page refuses

An empty box produces no SQL. The message is: Paste one value per line.

A list that would be IN () is refused even if you asked to keep blanks and then deleted them. An IN list needs at least one value. IN () is a syntax error.

The page stops at 1,000 values. This page builds at most 1,000 values.

It also stops at 100,000 characters of input. The list is over 100,000 characters. This page stops there.

Both limits are the page. A database will accept a longer IN list until it hits its own packet or query limit. A list that long is a sign you wanted a temporary table or a join, not a literal pasted into a query window.

When a parameter is the better tool

This page is for values you already trust. You copied them out of a spreadsheet, a report, or your own notes, and you are about to run the query yourself.

It is the wrong tool for a value a visitor typed. Building SQL by concatenating strings is how SQL injection works. The SQL injection post walks that failure with a real query. A placeholder, one per value or one array parameter where the driver supports it, keeps the values out of the SQL text. The database never parses them as syntax.

The SQL introduction is the wider map if IN itself is new. IN is a predicate. It is true when the left side equals any item in the list. NOT IN is the opposite, and it gets surprising when the list contains NULL, because NULL makes the comparison unknown. If you see a NULL in the list this page built, read that line again before you add NOT.

The CSV to SQL converter is the neighbouring tool and a different shape. It reads columns and writes CREATE TABLE plus INSERT. Use it when you have a table to load. Use this page when you have one column and a list of values to match.

The SQL formatter will wrap the finished query once you have pasted the list into it. Format after you build the list, not before. A formatter does not fix a quote that was escaped the wrong way.

The values stay in the browser. The URL remembers the column, the quoting mode, and whether blank lines are kept. It does not remember the values.

Try the SQL IN List Builder

Paste one value per line and get a SQL IN list. Quotes inside a value are doubled. Numbers can stay bare. The list stays in the browser.

Open the SQL IN List Builder →

Related tools

• CSV to SQL Converter: Paste CSV, get CREATE TABLE and INSERT statements — type inference, dialect quoting, batching, and NULL handling.
• SQL Formatter: Pretty-print SQL in your browser — keyword casing, clause breaks, indentation, or compact minify.

More tool tutorials →

Want me to talk about your product? You can sponsor this site.