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 excepta
,b
, orc
. - \
- Removes the special significance of the next character (to match
a literal \ or any other wildcard by specifying
\\
or\*
or\?
and so forth)
tennis
,
alone or within a longer string, such as tennis ball
or table
tennis
paddle
: WHERE description MATCHES '*tennis*'
TRUE
for the names Frank
and frank
:
WHERE fname MATCHES '[Ff]rank'
TRUE
for any name that
begins with either F
or f
: WHERE fname MATCHES '[Ff]*'
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.
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.