Quotation marks and escape characters

An escape character indicates to the preprocessor that it should print the character as a literal character instead of interpreting it. You can use the escape character with an interpreted character to make the compiler escape, or ignore, the interpreted meaning.

In ANSI SQL, the backslash character (\) is the escape character. To search for data that begins with the string \abc, the WHERE clause must use an escape character as follows:

... where col1 = '\\abc';

However, ANSI standards specify that using the backslash character (\) to escape single (' ') or double (" ") quotation marks is invalid. For example, the following attempt to find a quotation mark does not conform to ANSI standards:

... where col1 = '\'';

In non-embedded tools such as DB-Access, you can escape a quotation mark with either of the following methods:

You can use the same quotation mark as an escape character, as follows:
```
... where col1 = '''';
```
You can use an alternative quotation mark. For example, to look for a double quotation mark, you can enclose this double quotation mark with quotation marks, as follows:
```
... where col1 = ' "';
```

The following figure shows a SELECT statement with a WHERE clause that contains a double quotation mark enclosed with quotation marks.

Figure 1: A SELECT statement with an invalid WHERE clause

EXEC SQL select col1 from tab1 where col1 = ' "';

For the WHERE clause in Escaped query string as it is processed, the preprocessor does not process a double quotation mark; it passes it on to the C compiler. When the C compiler receives the string ' " ' (double quotation mark enclosed with quotation marks), it interprets the first quotation mark as the start of a string and the double quotation mark as the end of a string. The compiler cannot match the quotation mark that remains and therefore generates an error.

To cause the C compiler to interpret the double quotation mark as a character, precede the double quotation mark with the C escape character, the backslash (\). The following example illustrates the correct syntax for the query in Escaped query string as it is processed:

EXEC SQL select col1 from tab1 where col1 = '\"';

Because both C and ANSI SQL use the backslash character as the escape character, be careful when you search for the literal backslash in embedded queries. The following query shows the correct syntax to search for the string "\" (where the double quotation marks are part of the string):

EXEC SQL select col1 from tab1 where col1 = '\"\\\\\"';

This string requires five backslashes to obtain the correct interpretation. Three of the backslashes are escape characters, one for each double quotation mark and one for the backslash. The following table shows the string after it passes through each of the processing steps.

Table 1. Escaped query string as it is processed
Processor	After processing
ESQL/C preprocessor	'\"\\\\\"'
C compiler	'"\\"'
ANSI-compliant database server	'"\"'

supports strings in either quotation marks ('string') or double quotation marks ("string"). However, the C language supports strings only in double quotation marks. Therefore, the preprocessor converts every statement in the source file into a double-quoted string.