List to Array: Quoting, Escaping and the Syntax Each Language Expects

It is one of the most common small chores in programming: you have a column of values from a spreadsheet, a log or a chat message, and you need it as an array in code or as an IN (…) list in a query. The transformation is trivial. The details that break things are which brackets, which quotes, and what to do with values that are not plain words.

One list, many syntaxes

Which quotes go where

The SQL row causes the most confusion. MySQL accepts double-quoted strings by default, PostgreSQL and Oracle do not, and a query that works on one fails on the other with an "unknown column" error. Single quotes work everywhere.

Should numbers be quoted?

If every item is a number, the unquoted form [1,2,3] is usually what you want. Three cases are exceptions, and in each one the value only looks like a number:

• Codes with leading zeros. ZIP codes, phone numbers and product codes such as 00501. Unquoted, the zeros are dropped, and in strict JSON a leading zero is a syntax error.
• Very large IDs. A 64-bit ID such as 9007199254740993 does not fit in a JavaScript number and comes back as 9007199254740992. Keep it as a string.
• Columns that are text in the database. Comparing a VARCHAR column with unquoted numbers forces a type conversion on every row, which can skip the index and, in MySQL, match unexpected values. Quote the items so the types agree.

Values that contain quotes

Wrapping each item in quotes works until an item contains that quote character. Each language has its own escape:

The simplest fix is often to switch quote styles: if the data has apostrophes, use double quotes, and the reverse. That works for JavaScript and Python but not for JSON or SQL, where the quote character is fixed.

SQL IN lists: fine for ad-hoc queries, not for application code

Pasting a generated list into a query you run yourself in a console is normal and safe. Building the same string inside an application from user-supplied values is how SQL injection happens. In code, pass the list as a parameter:

SELECT * FROM orders WHERE id = ANY($1);   -- $1 = [101, 102, 103]

placeholders = ", ".join(["%s"] * len(ids))
cursor.execute(f"SELECT * FROM orders WHERE id IN ({placeholders})", ids)

Even for ad-hoc work, long lists run into limits:

• Oracle allows at most 1,000 expressions in one IN list.
• SQL Server allows about 2,100 parameters per statement.
• Everywhere, very long lists are slow to parse and plan. Past a few thousand values, load them into a temporary table and join.

Clean the list before you wrap it

• Remove duplicates unless repeats are meaningful. A duplicate in an IN list is harmless; a duplicate in test data or a config array usually is not.
• Decide on case. Apple and apple are different strings. Deduplicating case-insensitively keeps the first spelling and drops the rest.
• Sort when order has no meaning. A sorted array is easier to review and produces smaller diffs in version control when the list changes later.
• Check the count. Compare the number of items in the result with the number of rows you copied. A mismatch means a blank line, a merged cell or an item that was split.

Doing it in code

const items = text.split(/\r?\n/).map(s => s.trim()).filter(Boolean);
JSON.stringify(items);            // ["apple","banana","cherry"], with correct escaping

items = [line.strip() for line in text.splitlines() if line.strip()]
json.dumps(items)                 # JSON
repr(items)                       # Python literal

Both handle escaping for you, which is the main reason to prefer a serializer over string concatenation when the values are not simple.

For a quick conversion

The converter also removes duplicates, sorts, and shows the count (plus sum and average for numeric lists). To check two lists against each other first, see comparing two lists.