MATCHES Operator

The MATCHES operator is the Informix® extension for comparing a column value to another column value, or to a quoted string.

The MATCHES operator supports these 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 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 Informix® GLS User's Guide.

In a NLSCASE INSENSITIVE database, comparison operations on NCHAR and NVARCHAR data disregard lettercase differences, so that the database server treats case variants among strings composed of same sequence letters as duplicates. All pairs of the following strings return TRUE as operands of the MATCHES operator:

'beta' 'Beta' 'BETA' 'bETa' 'betA' 'BetA'

For more information, see Duplicate rows in NLSCASE INSENSITIVE databases and NCHAR and NVARCHAR expressions in case-insensitive databases.