Spain traditionally treats 'ch', 'll' as well as 'ñ' as letters of their own, ordered after c, l and n respectively. Example 9-9 illustrates the effect of using a Spanish sort against the employee names Chen and Chung.
Example 9-9 Variations in Linguistic Sorting (Binary and Spanish)
SQL> ALTER SESSION SET NLS_SORT=binary;
Session altered.
SQL> SELECT employee_id EmpID,
2 last_name "Last Name"
3 FROM employees
4 WHERE last_name LIKE 'C%'
5* ORDER BY last_name
EMPID Last Name
---------- -------------------------
187 Cabrio
148 Cambrault
154 Cambrault
110 Chen
188 Chung
119 Colmenares
6 rows selected.
SQL> ALTER SESSION SET NLS_SORT=spanish_m;
Session altered.
SQL> SELECT employee_id EmpID,
2 last_name "Last Name"
3 FROM employees
4 WHERE last_name LIKE 'C%'
5* ORDER BY last_name
EMPID Last Name
---------- -------------------------
187 Cabrio
148 Cambrault
154 Cambrault
119 Colmenares
110 Chen
188 Chung
6 rows selected.