DEFAULTESCCHAR session environment option

You can use the DEFAULTESCCHAR session environment option of the SET ENVIRONMENT statement to override the current default escape character within character-string operands of LIKE or MATCHES expressions during the current session.

DEFAULTESCCHAR environment option

DEFAULTESCCHAR { 'char' | 'NONE' }
Element Description Restrictions Syntax
char A single character to set as the default escape character in LIKE or MATCHES expressions that include no ESCAPE clause Must be a single-byte character, and must be delimited between single (') or double (") quotation marks Literal Number asQuoted String

Usage

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.

Setting a default escape character for the current session

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 LIKE or MATCHES expressions in subsequent DML statements in the same session, the escape character value defined by the SET ENVIRONMENT DEFAULTESCCHAR statement overrides the setting of the DEFAULTESCCHAR configuration parameter. SET ENVIRONMENT DEFAULTESCCHAR statements in the current session, however, have no effect on how the database server evaluates LIKE or MATCHES expressions in other sessions, which can use the system default escape character in the onconfig file for LIKE or MATCHES expressions that include no ESCAPE clause, or can use a different setting of the DEFAULTESCCHAR option, if data records that those sessions process require a different escape character.

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';

Setting the DEFAULTESCCHAR to 'NONE'

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.

Only LIKE or MATCHES expressions that include no ESCAPE clause are affected by the DEFAULTESCCHAR session environment setting. For more information about escape characters in LIKE or MATCHES expressions, see the topics ESCAPE with LIKE and ESCAPE with MATCHES.