How to deal with a single quote character in SQL?
When trying to execute a statement with a single quote (or apostrophe) inside a string in a SQL client:
SELECT * FROM employees WHERE name = 'd'Artagnan'
you get an error:
ORA-00933: SQL command not properly ended
This is because single quote is treated as an opening or ending of a string.
The simplest solution is to use a single quote twice:
SELECT * FROM employees WHERE name = 'd''Artagnan'
The additional single quote is used as an escape character, which means it indicates that the next character shouldn’t be treated as a part of the statement.
In Oracle Database (since 10g) you can use quote operator (quote delimiter) using q'[]’ syntax:
SELECT * FROM employees WHERE name = q'[d'Artagnan]'
How to concatenate strings with a single quote?
If you would need to concatenate a string with a single quote you need to use four single quotes (first for opening the string, second as an escape character, third as the actual single quote and fourth to end the string):
SELECT * FROM employees WHERE name = 'd||''''||Artagnan'
Double pipe (||) serve as the Oracle and IBM DB2 function for concatenations, for other databases you may need to use CONCAT function (DB2, MySQL, SQL Server 2012) or + (SQL Server).
Alternatively, there are other methods to escape a character using concatenation:
Concatenation using CHR function to add characters from the ASCII character set. For a single quote use CHR(39) in Oracle and IBM DB2. In other databases you may need to use CHAR(39) function (SQL Server, MySQL).
SELECT * FROM employees WHERE name = 'd'||CHR(39)||'Artagnan'