MATCHES condition

A MATCHES condition tests for matching character strings.

The condition is true, or satisfied, when the value of the column to the left of the MATCHES keyword matches the pattern that a quoted string specifies to the right of the MATCHES keyword. You can use wildcard characters in the string. For example, you can use brackets to specify a range of characters. For more information about MATCHES, see the HCL OneDB™ Guide to SQL: Syntax.

When a MATCHES expression does not list a range of characters in the string, it specifies a literal match. For literal matches, the data type of the column determines whether collation considerations come into play, as follows:

For CHAR and VARCHAR columns, no collation considerations come into play.
For NCHAR and NVARCHAR columns, collation considerations might come into play, because these data types use localized order and the locale might define equivalence classes of collation.
For example, the localized order might specify that a and A are an equivalent class. That is, they have the same rank in the collation order. For more information about localized order, see Localized order.

The examples in the following table illustrate the different results that CHAR and NCHAR columns produce when a user specifies the MATCHES keyword without a range in a SELECT statement. These examples assume use of a nondefault locale that defines A and a in an equivalence class. It also assumes that col1 is a CHAR column and col2 is an NCHAR column in table mytable.


Query	Data type	Query results
SELECT * FROM mytable WHERE col1 MATCHES 'art'	CHAR	All rows in which column col1 contains the value 'art' with a lowercase a
SELECT * FROM mytable WHERE col2 MATCHES 'art'	NCHAR	All rows in which column col2 contains the value 'art' or 'Art'

When you use the MATCHES keyword to specify a range, collation considerations come into play for all columns with character data types. When the column to the left of the MATCHES keyword is an NCHAR, NVARCHAR, CHAR, VARCHAR, or LVARCHAR data type, and the string operand of the MATCHES keyword includes brackets ( [ ] ) to specify a range, sorting follows a localized order, if the locale defines one.

Important: When the database server determines the characters that fall within a range with the MATCHES operator, it uses the localized order, if DB_LOCALE or SET COLLATION has specified one, even for CHAR, LVARCHAR, and VARCHAR columns. This behavior is an exception to the rule that the database server uses code-set order for all operations on CHAR, LVARCHAR and VARCHAR columns, and localized order (if one is defined) for sorting operations on NCHAR and NVARCHAR columns.

Some simple examples show how the database server treats NCHAR, NVARCHAR, LVARCHAR, CHAR, and VARCHAR columns when you use the MATCHES keyword with a range in a SELECT statement. Suppose that you want to retrieve from the abonnés table the employee number, given name, and family name for all employees whose family name nom begins in the range of characters E through P. Also assume that the nom column is an NCHAR column. The following SELECT statement uses a MATCHES condition in the WHERE clause to pose this query:

SELECT numéro,nom,prénom 
   FROM abonnés
   WHERE nom MATCHES '[E-P]*'
   ORDER BY nom;

The rows for Étaix, Ötker, and Øverst appear in the query result because, in the localized order, as Data set for localized order of the abonnés table shows, the accented first letter of each name falls within the E through P MATCHES range for the nom column.


numéro	nom	prénom
13608	Étaix	Émile
13607	Hammer	Gerhard
13602	Hämmerle	Greta
13604	LaForêt	Jean-Nol
13610	LeMatre	Hélose
13613	Llanero	Gloria Dolores
13603	Montaña	José Antonio
13611	Oatfield	Emily
13605	Ötker	Hans-Jrgen
13614	Øverst	Per-Anders

If nom is a CHAR column, the query result is the same as when nom was an NCHAR column. The database server always uses localized order to determine what characters fall within a range, regardless of whether the column is CHAR or NCHAR.