Article
Quotes and apostrophes in a SQL IN list
A SQL IN clause wraps each value in quotes. A name that already contains an apostrophe will close the string early and break the query. Data Delimiter will wrap a pasted list. It will not guess the escape rules for your dialect. Check the output before it goes into a production script.
What usually breaks
O’Brien, a possessive, or a value copied from a document that used a curly apostrophe. The clause then ends in the middle of a name.
Numeric codes do not need quotes. Wrapping them is harmless in some dialects and a type error in others. Choose quotes in the tool to match the column.
What I do with the output
I scan for a quote inside a value before I paste. If one is there, I escape it in T-SQL as two single quotes, or I take the value out and handle it separately.
Data Delimiter is for the split, the wrap, the join, and the duplicates. The dialect-specific escape is still a review step.
Questions
Why does a SQL IN list break on an apostrophe?
The apostrophe closes the quoted string early. The rest of the name is then invalid SQL. Escape it in the dialect you are writing, or handle that value separately.
Does Data Delimiter escape apostrophes?
It wraps values and can remove duplicates. It does not guess dialect escape rules. Check the output before it goes into a production script.