BETWEEN conditions

The following SELECT statement uses a BETWEEN condition to retrieve only those rows in which the values of the nom column are in the inclusive range of the values of the two expressions that follow the BETWEEN keyword:

SELECT numéro,nom,prénom 
   FROM abonnés
   WHERE nom BETWEEN 'A' AND 'Z';

The query result depends on whether nom is a CHAR or NCHAR column. If nom is a CHAR column, the database server uses the code-set order of the default code set to retrieve the rows that the WHERE clause specifies. The following example of output shows the query results.

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-Nol
13610	LeMatre	Hélose
13613	Llanero	Gloria Dolores
13603	Montaña	José Antonio
13611	Oatfield	Emily
13609	Tiramis	Paolo Alfredo

Because the database server uses the code-set order for the nom values, as Data set for code-set order of the abonnés table shows, 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

However, if nom is an NCHAR column, the database server uses localized order to sort the rows. The following output shows the query results.

numro	nom	prnom
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-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
13609	Tiramis	Paolo Alfredo