Case-insensitive and Accent-insensitive Collations

View All Scripts Login to Run Script

Script Name Case-insensitive and Accent-insensitive Collations
Description This script uses the NLS_SORT session parameter to specify a case-insensitive collation and an accent-insensitive collation.
Area NLS
Referenced In Database Globalization Support Guide
Contributor Oracle
Created Wednesday November 30, 2016

Statement 1

Create table test2 with one VARCHAR2 column.

CREATE TABLE test2 (letter VARCHAR2(10))

Table created.

Statement 2

Insert character 'ä' into the table.

INSERT INTO test2 VALUES('ä')

1 row(s) inserted.

Statement 3

Insert character 'a' into the table.

INSERT INTO test2 VALUES('a')

1 row(s) inserted.

Statement 4

Insert character 'A' into the table.

INSERT INTO test2 VALUES('A')

1 row(s) inserted.

Statement 5

Insert character 'Z' into the table.

INSERT INTO test2 VALUES('Z')

1 row(s) inserted.

Statement 6
Query the table using ORDER BY clause. The query result shows the data ordered according to the binary collation BINARY by default, which is a case-sensitive and accent-sensitive collation. The uppercase letters come before lowercase letters (case-sensitive) and letters with diacritics appear last (accent-sensitive) in the query result.
```
SELECT * FROM test2 ORDER BY letter
```
```
LETTER
A
Z
a
ä

4 rows selected.
```
Statement 7
Set the session parameter NLS_SORT to BINARY_CI. BINARY_CI is a binary case-insensitive and accent-sensitive collation.
```
ALTER SESSION SET NLS_SORT=BINARY_CI
```
```
Statement processed.
```
Statement 8
Query the table using ORDER BY clause. The query result shows the data ordered according to the binary case-insensitive and accent-sensitive collation BINARY_CI. BINARY_CI collation considers diacritics but ignores case. The lowercase letters come before uppercase letters (case-insensitive) and letters with diacritics appear last (accent-sensitive) in the query result.
```
SELECT * FROM test2 ORDER BY letter
```
```
LETTER
a
A
Z
ä

4 rows selected.
```
Statement 9
Set the session parameter NLS_SORT to BINARY_AI. BINARY_AI is a binary case-insensitive and accent-insensitive collation.
```
ALTER SESSION SET NLS_SORT=BINARY_AI
```
```
Statement processed.
```
Statement 10
Query the table using ORDER BY clause. The query result shows the data ordered according to the binary case-insensitive and accent-insensitive collation BINARY_AI. BINARY_AI collation ignores both case and diacritics. Hence, the diacritics character 'ä' is sorted alongside the characters having the base letter 'a'. All the characters having the base letter 'a', irrespective of their case and accent, are listed before 'Z' in the query result.
```
SELECT * FROM test2 ORDER BY letter
```
```
LETTER
ä
a
A
Z

4 rows selected.
```

LETTER
A
Z
a
ä

LETTER
a
A
Z
ä

LETTER
ä
a
A
Z

Additional Information

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