LIKE Operator

LIKE is the ANSI/ISO standard operator for comparing a column value to another column value, or to a quoted string.

The LIKE operator supports these wildcard characters in the quoted string.

Wildcard: Effect
%: Matches zero or more characters
_: Matches any single character

Besides % and _, LIKE supports a third wildcard character when both the DEFAULTESCCHAR configuration parameter and the DEFAULTESCCHAR session environment variable are not set:

Wildcard: Effect
\: Removes the special significance of the next character (to match a literal % or _ or \ by specifying \% or \_ or \\ )

Using the backslash ( \ ) symbol as the default escape character (when DEFAULTESCCHAR is not set) is the HCL OneDB™ extension to the ANSI/ISO-standard for SQL. You can specify backslash ( \ ) symbol or some other ASCII character as the default escape character by setting the DEFAULTESCCHAR value to that character. For more information, see DEFAULTESCCHAR session environment option.

In an ANSI-compliant database, you can only use the LIKE escape character to escape a percent sign ( % ), an underscore ( _ ), or the escape character itself.

The following condition tests the description column for the string tennis, alone or in a longer string, such as tennis ball or table tennis paddle:

WHERE description LIKE '%tennis%' ESCAPE '\'

The next example tests description for rows containing an underscore character. Here the backslash ( \ ) escape character is necessary because underscore ( _ ) is a wildcard character.

WHERE description LIKE '%\_%' ESCAPE '\'

The LIKE operator has an associated operator function called like( ). You can define a like( ) function to handle your own user-defined data types. See also HCL OneDB User-Defined Routines and Data Types Developer's Guide.