The type of collation order that the database server uses affects the following SQL statements:

• CREATE INDEX
• SELECT

When you use a nondefault locale, the following locale-specific considerations apply to the CREATE INDEX statement:

• When you create an index on a CHAR or VARCHAR column, the index keys are stored in code-set order.

• When you create an index on an NCHAR or NVARCHAR column, the index keys are stored in localized order.

If you use the default locale (U.S. English), the index keys are stored in the code-set order (in ascending or descending sequence) of the default code set regardless of the data type of the character column. Because the default locale does not define a localized order, the database server sorts NCHAR and NVARCHAR columns, as well as CHAR and VARCHAR columns, in code-set order.

For a complete description of the CREATE INDEX statement, see the Informix Guide to SQL: Syntax.

Collation Order in SELECT Statements

• The ORDER BY clause
• The relational operator, BETWEEN, and IN conditions of the WHERE clause
• The MATCHES and LIKE conditions of the WHERE clause

The ORDER BY Clause

• CHAR and VARCHAR columns are sorted in code-set order.
• NCHAR and NVARCHAR columns are sorted in localized order.

SELECT numéro,nom,prénom 

	FROM abonnés

	ORDER BY nom;

Figure 3-2
Data Set for Code-Set Order of the abonné Table

numéro	nom	prénom
13612	Azevedo	Edouardo Freire
13606	Dupré	Michèle Françoise
13607	Hammer	Gerhard
13602	Hämmerle	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
13609	Tiramisù	Paolo Alfredo
13600	da Sousa	João Lourenço Antunes
13615	di Girolamo	Giuseppe
13601	Ålesund	Sverre
13608	Étaix	Émile
13605	Ötker	Hans-Jürgen
13614	Øverst	Per-Anders

However, the result of the query is different if the numéro, nom, and prénom columns of the abonnés table are defined as NCHAR rather than CHAR.

Suppose the nondefault locale defines a localized order that collates the data as Figure 3-3 shows. This localized order defines equivalence classes for uppercase and lowercase letters and for unaccented and accented versions of the same letter.

Figure 3-3
Data Set for Localized Order of the abonné Table

numéro	nom	prénom
13612	Azevedo	Edouardo Freire
13601	Ålesund	Sverre
13600	da Sousa	João Lourenço Antunes
13615	di Girolamo	Giuseppe
13606	Dupré	Michèle Françoise
13608	Étaix	Émile
13607	Hammer	Gerhard
13602	Hämmerle	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
13605	Ötker	Hans-Jürgen
13614	Øverst	Per-Anders
13609	Tiramisù	Paolo Alfredo

The same SELECT statement now returns the query results in localized order because the nom column that the ORDER BY clause specifies is an NCHAR column.

The SELECT statement supports use of a column substring in an ORDER BY clause. However, you need to ensure that this use for column substrings works with the code set that your locale supports. For more information, see "Partial Characters in Column Substrings".

Logical Predicates in a WHERE Clause

• Relational-operator condition
• BETWEEN condition
• IN condition
• EXISTS and ANY conditions

SELECT numéro,nom,prénom 

	FROM abonnés

	WHERE nom < 'Hammer';

These two rows are those less than Hammer in the code-set-ordered data set shown in Figure 3-2.

However, if nom is an NCHAR column, the database server uses localized order to sort the rows that the WHERE clause specifies. The following sample of output shows that this SELECT statement retrieves six rows.

numéro	nom	prénom
13612	Azevedo	Edouardo Freire
13601	Ålesund	Sverre
13600	da Sousa	João Lourenço Antunes
13615	di Girolamo	Giuseppe
13606	Dupré	Michèle Françoise
13608	Étaix	Émile

These six rows are those less than Hammer in the localized-order data set shown in Figure 3-3.

BETWEEN Conditions

SELECT numéro,nom,prénom 

	FROM abonnés

	WHERE nom BETWEEN 'A' AND 'Z';

Because the database server uses the code-set order for the nom values (see Figure 3-2), these query results do not include the following rows:

• Rows in which the value of nom begins with a lowercase letter: da Sousa and di Girolamo
• Rows with an accented letter: Ålesund, Étaix, Ötker, and Øverst

Because the database server uses localized order for the nom values, these query results include rows in which the value of nom begins with a lowercase letter or accented letter.

IN Conditions

SELECT numéro,nom,prénom 

	FROM abonnés

	WHERE nom IN ('Azevedo', 'Llanero', 'Oatfield');

However, if nom is an NCHAR column, the database server uses localized order (see Figure 3-3) to sort the rows. If the locale defines A, a, and Å as equivalent characters in the localized order, the query returns rows in which the value of nom is Azevedo, azevedo, or Åzevedo. The same selection rule applies to the other names in the parenthetical list that follows the IN keyword.

Comparisons with MATCHES and LIKE Conditions

• MATCHES condition
• LIKE condition

When the MATCHES condition 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 equal weight in the collation sequence. For further information on localized order, see page 1-10.

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, or VARCHAR column, and the quoted string to the right of the MATCHES keyword includes brackets to specify a range, the database server uses localized order.

Important: When the database server determines the characters that fall within a range, it always uses the localized order that is specified for the database, even for CHAR and VARCHAR columns. This behavior is an exception to the rule that the database server uses code-set order for all operations on CHAR and VARCHAR columns and uses localized order for all operations on NCHAR and NVARCHAR columns.

Some simple examples show how the database server treats NCHAR, NVARCHAR, 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, first name, and last name for all employees whose last 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;

If nom is a CHAR column, the query result is exactly 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.

LIKE Condition

The LIKE condition can only specify 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. For more information, see the description of literal matches with the MATCHES condition on page 3-28.

The LIKE keyword does not support matches with a range. That is, you cannot use bracketed wildcard characters in LIKE conditions.

Wildcard Characters in LIKE and MATCHES Conditions

For CHAR and VARCHAR data, the database server performs byte-by-byte comparison for pattern matching in the LIKE and MATCHES conditions. For NCHAR and NVARCHAR data, the database server performs pattern matching in the LIKE and MATCHES conditions based on characters, not bytes. Therefore, the _ (underscore) wildcard of the LIKE clause and the ? wildcard of the MATCHES clause match any one single-byte or multibyte character, as the following table shows.

Condition	Quoted String	Column Value	Result
LIKE	'ab_d'	'abcd'	True
LIKE	'ab_d'	'abA1A2d'	True
MATCHES	'ab?d'	'abcd'	True
MATCHES	'ab?d'	'abA1A2d'	True

The database server treats any multibyte character as a literal character. To tell the database server to interpret a wildcard character as its literal meaning, you must precede the character with an escape character. You must use single-byte characters as escape characters; the database server does not recognize use of multibyte characters for this purpose. The default escape character is the backslash (\).

The following use of the MATCHES clause gives a true result for the column value that is shown.

Clause	Quoted String	Column Value	Result
MATCHES	'ab\?d'	'ab?d'	True