Join lines with a separator

Eight separators over the same awkward list

The list used throughout this page is deliberately not clean. It has 8 lines: 1 of them blank, 1 padded with spaces, one containing an apostrophe and one containing double quotes. Those are precisely the four things that break a naive join, so the rows below are tests that can fail. None of them is a demonstration.

Every one of the 8 outputs still contains the apostrophe from O'Neill intact or correctly escaped: true. That is asserted rather than eyeballed, because an apostrophe silently eaten by a quoting routine is the classic way a name list corrupts a database import.

The two outputs that are checked, not just shown

Six of the presets produce plain text that can only be inspected. Two produce a format with rules, so this build checks them instead of asserting them — 2 of 2 passed.

JSON

The JSON preset wraps each value in double quotes and escapes backslashes and quotes inside it. The check is a parse: the output is wrapped in square brackets and handed to JSON.parse, and the result must be an array of exactly 7 elements. For the sample list that gives:

["Ada Lovelace", "O'Neill", "Grace Hopper", "Katherine Johnson", "Radia \"Radia\" Perlman", "Margaret Hamilton", "Barbara Liskov"]

Note what happened to Radia "Radia" Perlman. The inner double quotes became \", which is what makes the array parse. A join that simply wrapped each line in quotes would have produced invalid JSON at that row and valid JSON everywhere else — the worst possible failure mode, because it works on your test data.

SQL

The SQL preset wraps each value in single quotes and doubles any apostrophe inside it, which is the escape the SQL standard specifies. For the sample list:

SELECT * FROM people WHERE name IN ('Ada Lovelace', 'O''Neill', 'Grace Hopper', 'Katherine Johnson', 'Radia "Radia" Perlman', 'Margaret Hamilton', 'Barbara Liskov');

O'Neill comes out as 'O''Neill'. Without that doubling the apostrophe would close the string literal early and the rest of the value would be parsed as SQL — which is not merely a broken query but the shape of an injection bug.

What the check does and does not prove. There is no SQL engine in this build, so the SQL row is checked by counting quote characters for balance, not by executing it. That is a weaker guarantee than the JSON parse, and it is stated here in as many words. Escaping for a specific database — identifiers, backslash handling in MySQL, and anything user-supplied — is a job for that database's own parameter binding, not for a text tool. Use this to build a hand-run query, not to assemble SQL in production code.

Why blank lines are dropped by default

A blank line in a pasted column is almost always an artefact — the row after the last one, or a gap in a spreadsheet selection. Keeping it produces a, , b, which is a three-element list containing one empty string, and every consumer treats that differently. Dropping it produces a, b. The sample here has 1 blank line and comes out with 7 items rather than 8.

If the empty positions carry meaning — a fixed-length column where row 4 is genuinely blank — switch the option off and the gaps are preserved as empty items.

Trimming, and why it happens before the join

Values pasted out of a spreadsheet frequently carry leading or trailing spaces. Joined without trimming, ' Margaret Hamilton ' becomes a value with invisible padding that will not match anything, and in a SQL list it becomes an entry that silently returns no rows. Every line is trimmed of leading and trailing spaces and tabs before the separator is applied, and interior runs of spaces are collapsed to one.

No-break spaces are the exception, and deliberately so: they are content until you say otherwise. If a value came out of a word processor and will not match, press Show invisible characters — an NBSP badge at the edge of a value explains it — then switch on the no-break space conversion.

Related jobs

To turn one delimited line back into a column, or to strip breaks from inside spreadsheet cells without destroying the rows, see the CSV page. To clean the column before joining it — blank lines, padding, stray no-break spaces — remove blank lines and tidy the spaces first; the two operations compose cleanly in that order.