String literals in SQL statements

Single quotes is the standard for delimiting string literals in SQL.

Some database servers like IBM® Informix® allow single and double quoted string literals in SQL statements, both are equivalent:

SELECT COUNT(*) FROM table
 WHERE col1 = "abc'def""ghi"
   AND col1 = 'abc''def"ghi'

Most database servers do not support this specific feature.

Table 1. Database servers support of double-quoted string literals
Database Server Type	Double quoted string literals
IBM DB2® LUW	No
IBM Informix	Yes
Microsoft™ SQL Server	Yes
Oracle® MySQL	No
Oracle Database Server	No
PostgreSQL	No
SAP HANA®	No
SQLite	Yes

The ANSI SQL standards define doubles quotes as database object names delimiters, while single quotes are dedicated to string literals:

CREATE TABLE "my table" ( "column 1" CHAR(10) ) 
SELECT COUNT(*) FROM "my table" WHERE "column 1" = 'abc'

If you want to write a single quote character inside a string literal, you must write 2 single quotes:

... WHERE comment = 'John''s house'

When writing static SQL in your programs, the double quoted string literals as converted to ANSI single quoted string literals by the fglcomp compiler. However, dynamic SQL statements are not parsed by the compiler and therefore need to use single quoted string literals.

We recommend that you always use single quotes for string literals and, if needed, double quotes for database object names.