DEFAULTESCCHAR session environment option

The DEFAULTESCCHAR session environment option can be set to one of the following values:

'char' or "char"

A single-byte ASCII character, delimited by single ( ' ) or double ( " ) quotation marks, that designates the default escape character for this session in LIKE or MATCHES expressions that include no ESCAPE clause.

'NONE' or "NONE"

A case-insensitive quoted string, indicating that in this session there is no default escape character for LIKE or MATCHES expressions that include no ESCAPE clause.

This is the descending order of precedence, from highest to lowest, among the methods for specifying an escape character for LIKE or MATCHES expressions:

• The character following the ESCAPE keyword of the LIKE or MATCHES expression
• The character following the DEFAULTESCCHAR keyword in the SET ENVIRONMENT statement (for the current session only)
• The character setting of the DEFAULTESCCHAR configuration parameter
• The system default backslash ( \ ) character.

An escape character instructs the SQL parser to interpret as a literal character (rather than as having special significance for the next character) any characters that can be wildcard characters. For example, default wildcards are

• % and _ for operands of the LIKE operator,
• or * and ^ for operands of the MATCHES operator.

In a LIKE or MATCHES expression, the escape character must immediately precede the character whose special significance is to be ignored.

For example, in subsequent LIKE and MATCHES expressions in the same session that include no ESCAPE clause, the following statement makes '#' the default escape character:

SET ENVIRONMENT DEFAULTESCCHAR '#'; 

In the following WHERE clause with a LIKE expression, the previous SET ENVIRONMENT statement causes the SQL parser to interpret the three backslash ( \ ) symbols as literal characters in the file path, rather than as escape characters:

SELECT pathname FROM OldFiles2014
   WHERE pathname LIKE 'C:#\user#\argos#\collars';

To treat as a literal character the system default escape character ( \ ), and the default escape character set by the DEFAULTESCCHAR configuration parameter, and any session default escape character previously set by SET ENVIRONMENT DEFAULTESCCHAR in the current session, you can specify 'NONE' as the setting of the DEFAULTESCCHAR option:

SET ENVIRONMENT DEFAULTESCCHAR 'NONE';

When the 'NONE' setting is in effect, the database server has the following behavior when evaluating LIKE or MATCHES expressions during the session:

• In character-string operands of LIKE or MATCHES expressions, any escape character that an SQL statement uses to mark a wildcard symbol as a literal character must be defined in the ESCAPE clause of the same LIKE or MATCHES expression.
• LIKE or MATCHES expressions that include no ESCAPE clause must prefix wildcard characters that are used as literals with the default escape characters of the LIKE or MATCHES operators, or with the backslash ( \ ) character.
• In LIKE or MATCHES expressions, any other escape character that the DEFAULTESCCHAR configuration parameter defines in the ONCONFIG file is treated as a literal character, rather than as an escape character.