Format Code Now
/
TextSQL IN List Builder Next: String Length Calculator ›

SQL IN List Builder

TX-03

Copy a column from Excel, a log or a query result, paste it here, and get a WHERE-ready IN (...) clause. It quotes text, leaves numbers bare, and can split a long list for Oracle's 1000 item limit.

Runs in your browser. Nothing is uploaded.

Values, one per line

SQL

How to use it

  1. Paste one value per line into the input box. Tick First line is the column name if line 1 is a header.
  2. Choose how to quote values: Auto keeps numbers bare and quotes the rest.
  3. Choose what to do with NULL, whether to remove duplicates, and whether text gets the SQL Server N prefix.
  4. Set Split every to 1000 for Oracle, or pick NOT IN or one value per line.
  5. Copy the SQL into your query.

Quoting and escaping

With Auto, a value that looks like a plain number, such as 42, -7 or 3.5, is written bare, and everything else is wrapped in single quotes with any single quote inside doubled, so O'Brien becomes 'O''Brien'. A value with a leading zero, such as 007 or a ZIP code, is quoted so the zero is not lost. Backslashes are not changed, so check MySQL output. For SQL Server, tick the N prefix to write N'text' for nvarchar columns.

NULL values

A NULL inside an IN list never matches a row, because NULL is not equal to anything. Inside NOT IN it is worse: one NULL makes the whole condition return no rows. Choose Remove and add OR IS NULL to take NULL out of the list and add a separate IS NULL test, or IS NOT NULL for NOT IN. You can also drop NULL values, treat the word as plain text, or keep them as they are.

Long lists and column names

Oracle rejects an IN list of more than 1000 items. Set Split every to 1000 and the result becomes several column IN (...) tests joined with OR inside brackets, or with AND for NOT IN. Other databases have their own limits, so 500 and 100 are offered too. Without a column name on the first line, the result uses column_name as a placeholder for you to replace.

Error messages and what to do

MessageWhat it meansFix
No values found. Put one value per line.The input is empty after blank lines are skipped, or every value was a removed NULL.Paste at least one value, one per line.
"order id" does not look like a column name. Use a name such as customer_id or t.id, or untick "First line is the column name".The first line was used as the column name but it contains spaces or other characters.Fix the header, or untick the option so the first line counts as a value.
The value "a b" contains spaces, a quote or punctuation and cannot go in the list unquoted. Choose "Auto" or "Quote everything".No quotes was chosen but a value needs them.Switch Quotes to Auto or Quote everything.

Questions people ask

How do I turn an Excel column into a SQL IN list?

Select the column in Excel, copy it and paste it here. Each line becomes one value, numbers are left bare and text is quoted. Copy the result into your WHERE clause. Cells that are empty are skipped.

How do I get around Oracle's limit of 1000 items in an IN list?

Set Split every to 1000 values. The tool writes several IN lists for the same column and joins them with OR inside brackets. The query is longer but stays under the limit.

What does the N prefix do?

In SQL Server, N'text' marks a string as Unicode (nvarchar). Without it, a literal with accents or non-Latin letters may be converted using the database code page. Tick the option to put N in front of every quoted value.

Why is my NOT IN list returning no rows?

If the list contains a NULL, NOT IN returns no rows, because comparing with NULL is never true. Choose Remove and add OR IS NULL, or Remove them, so the list has no NULL in it.

Are numbers with leading zeros quoted?

Yes, in Auto mode. A value such as 007 or 02134 is treated as text, so it is quoted and keeps its zeros. A plain number like 7 or 3.5 stays bare. Pick Quote everything if your column is text throughout.

Does it remove duplicates and trim spaces?

Trimming is on by default and removes spaces around each value. Remove duplicates is off by default, so your list is not changed unless you ask. When it is on, the first copy of each value stays and the order is kept.

About this tool

Each non-empty line becomes one value. Auto quoting leaves plain numbers bare and quotes everything else, so a value with a leading zero such as 007 stays text. Single quotes inside text are doubled; backslashes are not changed, so check MySQL output. Without a column name on the first line the result uses column_name as a placeholder. Oracle allows at most 1000 items in an IN list, so use Split every to join several lists with OR. A NULL left inside IN never matches a row, and one inside NOT IN makes the whole test return no rows. Use the IS NULL option for that.

Format Code Now: free, no ads, no accounts. Your input stays in your browser.