MATCHES clause and non-default locales

By default, HCL Informix® database servers use the U.S. English language environment, called a locale, for database data. This default locale uses the ISO 8859-1 code set. The U.S. English locale specifies that MATCHES will use code-set order.

If your database uses a non-default locale, a MATCHES clause that specifies a range uses the collation order of that locale for character data types (including CHAR, NCHAR, VARCHAR, NVARCHAR, and LVARCHAR). This feature of MATCHES ranges is an exception to the general rule that only NCHAR and NVARCHAR columns can use locale-specific collation. If the locale does not specify any special collation order, however, then MATCHES uses the code-set order.

With HCL Informix, you can use the SET COLLATION statement to specify a database locale for your session that is different from the DB_LOCALE setting. See the HCL Informix Guide to SQL: Syntax for a description of SET COLLATION.

Figure 1. Query

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

In the result, the rows for Étaix, Ötker, and Øverst are not selected and listed because, with ISO 8859-1 code-set order, the accented first letter of each name is not in the E through P MATCHES range for the nom column.

Figure 2. Query result

numéro        nom            prénom

13607      Hammer            Gerhard
13602      Hämmer            Greta
13604      LaForêt           Jean-Noⅇl
13610      LeMa⁣tre          Héloïse
13613      Llanero           Gloria Dolores
13603      Montaña           José Antonio
13611      Oatfield          Emily

For more information on non-English data and locales, see the HCL Informix GLS User's Guide.