LIKE and MATCHES Condition

A LIKE or MATCHES condition tests for matching character strings. The condition is TRUE, or satisfied, when either of the following tests is TRUE:

• The value of the column on the left matches the pattern that the quoted string specifies. You can use wildcard characters in the string. NULL values do not satisfy the condition.
• The value of the column on the left matches the pattern that the column on the right specifies. The value of the column on the right serves as the matching pattern in the condition.

You can use the single quote ( ' ) only with the quoted string to match a literal quote; you cannot use the ESCAPE clause. You can use the quote character as the escape character in matching any other pattern if you write it as ''''.

NOT Operator

The NOT operator makes the search condition successful when the column on the left has a value that is not NULL and that does not match the pattern that the quoted string specifies.

For example, the following conditions exclude all rows that begin with the characters Baxter in the lname column:

WHERE lname NOT LIKE 'Baxter%'
WHERE lname NOT MATCHES 'Baxter*'

LIKE Operator

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

Wildcard

Effect

%

Matches zero or more characters

_

Matches any single character

\

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 is an Informix extension to the ANSI/ISO-standard for SQL.

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

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

WHERE description LIKE '%tennis%'

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

WHERE description LIKE '%\_%'

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 IBM Informix User-Defined Routines and Data Types Developer's Guide.

MATCHES Operator

The MATCHES operator supports wildcard characters in the quoted string.

Wildcard

Effect

*

Matches any string of zero or more characters

?

Matches any single character

[ . . . ]

Matches any of the enclosed characters, including ranges, as in [a-z].
Characters within the brackets cannot be escaped.

^

As first character within the brackets, matches any character that is not listed. Thus, [^abc] matches any character except a, b, or c.

\

Removes the special significance of the next character (to match a literal \ or any other wildcard by specifying \\ or\* or \? and so forth)

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

WHERE description MATCHES '*tennis*'

The following condition is TRUE for the names Frank and frank:

WHERE fname MATCHES '[Ff]rank'

The following condition is TRUE for any name that begins with either F or f:

WHERE fname MATCHES '[Ff]*'

The next condition is TRUE for any name that ends with the letters a, b, c, or d:

WHERE fname MATCHES '*[a-d]'

MATCHES has an associated matches( ) operator function. You can define a matches( ) function for your own user-defined data types. For more information, see IBM Informix User-Defined Routines and Data Types Developer's Guide.

If DB_LOCALE or SET COLLATION specifies a nondefault locale supporting a localized collation, and you specify a range for the MATCHES operator using bracket ( [ . . . ] ) symbols, the database server uses the localized collating order, instead of code-set order, to interpret the range and to compare values that have CHAR, CHARACTER VARYING, LVARCHAR, NCHAR, NVARCHAR, and VARCHAR data types.

This behavior is an exception to the usual rule that only NCHAR and NVARCHAR data types can be compared in a localized collating order. For more information on the GLS aspects of conditions that include the MATCHES or LIKE operators, see the IBM Informix GLS User's Guide.

ESCAPE with LIKE

The ESCAPE clause can specify a nondefault escape character. For example, if you specify z in the ESCAPE clause, then a quoted string operand that included z_ is interpreted as including a literal underscore ( _ ) character, rather than _ as a wildcard. Similarly, z% is interpreted as a literal percent ( % ) sign, rather than % as a wildcard. Finally, the characters zz in a string would be interpreted as single literal z. The following statement retrieves rows from the customer table in which the company column includes a literal underscore character:

SELECT * FROM customer WHERE company LIKE '%z_%' ESCAPE 'z'

You can also use a host variable that contains a single character. The next statement uses a host variable to specify an escape character:

EXEC SQL BEGIN DECLARE SECTION;
   char escp='z';
   char fname[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL select fname from customer 
   into :fname where company like '%z_%' escape :escp;

ESCAPE with MATCHES

The ESCAPE clause can specify a nondefault escape character. Use this as you would the backslash to include a question mark ( ? ), an asterisk ( * ), a caret ( ^ ), or a left ( [ ) or right ( ] ) bracket as a literal character within the quoted string, to prevent them from being interpreted as special characters. If you choose to use z as the escape character, the characters z? in a string stand for a literal question mark ( ? ). Similarly, the characters z* stand for a literal asterisk ( * ). Finally, the characters zz in the string stand for the single character z.

The following example retrieves rows from the customer table in which the value of the company column includes the question mark ( ? ):

SELECT * FROM customer WHERE company MATCHES '*z?*' ESCAPE 'z'