Linguistic Collations

View All Scripts Login to Run Script

Script Name Linguistic Collations
Description This script queries language specific data from a table using a binary collation, a monolingual collation, and a UCA collation.
Area NLS
Referenced In Database Globalization Support Guide
Contributor Oracle
Created Wednesday November 30, 2016

Statement 1

Create table test3 with one VARCHAR2 column.

CREATE TABLE test3 (name VARCHAR2(20))

Table created.

Statement 2

Insert value 'Diet' in the table.

INSERT INTO test3 VALUES('Diet')

1 row(s) inserted.

Statement 3

Insert value 'À voir' in the table.

INSERT INTO test3 VALUES('À voir')

1 row(s) inserted.

Statement 4

Insert value 'Freizeit' in the table.

INSERT INTO test3 VALUES('Freizeit')

1 row(s) inserted.

Statement 5
(Binary Collation Example) Query the table using ORDER BY clause. The query result shows the data ordered according to the binary collation by default. Note that 'À voir' is displayed at the end of the query result.
```
SELECT * FROM test3 ORDER BY name
```
```
NAME
Diet
Freizeit
À voir

3 rows selected.
```
Statement 6
(Monolingual Collation Example) Query the table using ORDER BY clause. Use the NLSSORT function and the NLS_SORT parameter to specify the monolingual collation 'GERMAN'. The query result shows the data ordered according to the 'GERMAN' collation. Note that 'À voir' is displayed at the beginning of the query result.
```
SELECT * FROM test3 ORDER BY NLSSORT(name, 'NLS_SORT=GERMAN')
```
```
NAME
À voir
Diet
Freizeit

3 rows selected.
```
Statement 7
Insert the character string 'Đñ' into the table.
```
INSERT INTO test3 VALUES('Đñ')
```
```
1 row(s) inserted.
```
Statement 8
Query the table using ORDER BY clause. Use the NLSSORT function and the NLS_SORT parameter to specify the monolingual collation 'GERMAN'. Note that the character string 'Đñ' is displayed at the end of the query result, because these characters are not recognized by the 'GERMAN' collation.
```
SELECT * FROM test3 ORDER BY NLSSORT(name, 'NLS_SORT=GERMAN')
```
```
NAME
À voir
Diet
Freizeit
Đñ

4 rows selected.
```
Statement 9
(UCA Collation Example) Query the table using ORDER BY clause. Use the NLSSORT function and the NLS_SORT parameter to specify the UCA collation 'UCA0620_DUCET'. The query result shows the data ordered according to the 'UCA0620_DUCET' collation. Note that the query result shows 'Đñ' immediately after 'Diet'.
```
SELECT * FROM test3 ORDER BY NLSSORT(name, 'NLS_SORT=UCA0620_DUCET')
```
```
NAME
À voir
Diet
Đñ
Freizeit

4 rows selected.
```

NAME
Diet
Freizeit
À voir

NAME
À voir
Diet
Freizeit

NAME
À voir
Diet
Freizeit
Đñ

NAME
À voir
Diet
Đñ
Freizeit

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database