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
Genero db	No
IBM DB2^® UDB	No
IBM Informix	Yes
Microsoft™ SQL Server	Yes
MySQL	No
Oracle Database Server	No
PostgreSQL	No
Sybase ASE	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.