Oracle Live SQL - Script: Case- and accent-insensitive searching and sorting examples

Statement 1

You can do case insensitive search by upper- or lower-casing the column and search string

Case insensitive search by converting case

select athlete_name   
from   olym.olym_athletes  
where  upper ( athlete_name ) like upper ( '%barry%' )  
order  by athlete_name


ATHLETE_NAME
BARRY, Kevin
BARRY, William Louis
DAGGER, Barry Edward
DANCER, Barry
DAVIS, Barry Alan
DEMET-BARRY, Deirdre
KELLY, Barry
MAGEE, Arthur Barry
MAISTER, Barry John
WEITZENBERG, Charles Barry

10 rows selected.

ATHLETE_NAME
BARRY, Kevin
BARRY, William Louis
DAGGER, Barry Edward
DANCER, Barry
DAVIS, Barry Alan
DEMET-BARRY, Deirdre
KELLY, Barry
MAGEE, Arthur Barry
MAISTER, Barry John
WEITZENBERG, Charles Barry

Statement 2

From Oracle Database 12.2, you can use the collate operator to specify a collation. This defines the search and sort semantics for the comparison. BINARY_CI is a case-insensitive collation, so this does a case-insensitive search

Case insensitive search using collate

select athlete_name   
from   olym.olym_athletes  
where  athlete_name like '%barry%'   
         collate binary_ci  
order  by athlete_name


ATHLETE_NAME
BARRY, Kevin
BARRY, William Louis
DAGGER, Barry Edward
DANCER, Barry
DAVIS, Barry Alan
DEMET-BARRY, Deirdre
KELLY, Barry
MAGEE, Arthur Barry
MAISTER, Barry John
WEITZENBERG, Charles Barry

10 rows selected.

ATHLETE_NAME
BARRY, Kevin
BARRY, William Louis
DAGGER, Barry Edward
DANCER, Barry
DAVIS, Barry Alan
DEMET-BARRY, Deirdre
KELLY, Barry
MAGEE, Arthur Barry
MAISTER, Barry John
WEITZENBERG, Charles Barry

Statement 3

This counts the athletes with family names starting with the letter N or later in the alphabet

Case insensitive range comparison

select count (*)  
from   olym.olym_athletes  
where  athlete_name >= 'n'   
         collate binary_ci


COUNT(*)
8203

COUNT(*)
8203

Statement 4

Lowercase characters sort after uppercase, so who are the athletes with names starting with lowercase letters?

Exact text range comparison

select count (*)   
from   olym.olym_athletes   
where  athlete_name >= 'n'


COUNT(*)
12

COUNT(*)
12

Statement 5

Accented characters are after all upper- and lowercase characters in binary collations. So this search includes people whose name starts with Ö (uppercase O-umlaut).

select athlete_name  
from   olym.olym_athletes  
where  athlete_name >= 'n'


ATHLETE_NAME
ÖRSTED, Hans-Henrik
ÖRTEGREN, Ruben
ÖRVIG, Erik
ÖRVIG, Olav
ÖRVIG, Thor
ÖSTENSEN, Östen
ÖSTERVOLD, Henrik
ÖSTERVOLD, Jan Olsen
ÖSTERVOLD, Kristian Olsen
ÖSTERVOLD, Ole Olsen
ÖSTMO, Ole
ÖSTRAND, Per-Olof

12 rows selected.

ATHLETE_NAME
ÖRSTED, Hans-Henrik
ÖRTEGREN, Ruben
ÖRVIG, Erik
ÖRVIG, Olav
ÖRVIG, Thor
ÖSTENSEN, Östen
ÖSTERVOLD, Henrik
ÖSTERVOLD, Jan Olsen
ÖSTERVOLD, Kristian Olsen
ÖSTERVOLD, Ole Olsen
ÖSTMO, Ole
ÖSTRAND, Per-Olof

Statement 6

You can also use collate to specify accent-insensitive searches. To do this, specify an accent-insensitive collation, such as BINARY_AI. This includes athletes with é (e-acute) or è (e-grave) in their name. Note this is both case- and accent-insensitive!

Accent insensitve search

select substr (   
         athlete_name,   
         instr ( athlete_name, ', ' ) + 2   
       ) given_names,  
       athlete_name  
from   olym.olym_athletes  
where  athlete_name like '%helene%'   
         collate binary_ai  
order  by given_names


GIVEN_NAMES ATHLETE_NAME
Helene CORTIN, Helene
Helene JUNKER, Helene
Helene MADISON, Helene
Helene MAYER, Helene
Helene "Leni" SCHMIDT, Helene "Leni"
Hélène PREVOST, Hélène
Marie-Helene PREMONT, Marie-Helene

7 rows selected.

GIVEN_NAMES	ATHLETE_NAME
Helene	CORTIN, Helene
Helene	JUNKER, Helene
Helene	MADISON, Helene
Helene	MAYER, Helene
Helene "Leni"	SCHMIDT, Helene "Leni"
Hélène	PREVOST, Hélène
Marie-Helene	PREMONT, Marie-Helene

Statement 7

You can use the collate operator in the order by clause. This does an accent-insensitive sort of the athlete's given names, so Hélène has the same sort value as Helene. This makes the output non-deterministic - Hélène could appear anywhere within the Helenes

Change how accented characters are sorted

select substr (   
         athlete_name, instr ( athlete_name, ',' ) + 2   
       ) given_names,  
       athlete_name  
from   olym.olym_athletes  
where  athlete_name like '%helene%'   
         collate binary_ai  
order  by given_names  
  collate binary_ai


GIVEN_NAMES ATHLETE_NAME
Helene CORTIN, Helene
Helene JUNKER, Helene
Helene MADISON, Helene
Helene MAYER, Helene
Hélène PREVOST, Hélène
Helene "Leni" SCHMIDT, Helene "Leni"
Marie-Helene PREMONT, Marie-Helene

7 rows selected.

GIVEN_NAMES	ATHLETE_NAME
Helene	CORTIN, Helene
Helene	JUNKER, Helene
Helene	MADISON, Helene
Helene	MAYER, Helene
Hélène	PREVOST, Hélène
Helene "Leni"	SCHMIDT, Helene "Leni"
Marie-Helene	PREMONT, Marie-Helene

Statement 8

You can also specify collations in the select clause. Sorting by the column alias uses the collation of this expression.

select substr (   
         athlete_name, instr ( athlete_name, ',' ) + 2   
       ) collate binary_ai given_names,  
       athlete_name  
from   olym.olym_athletes  
where  athlete_name like '%helene%'   
         collate binary_ai  
order  by given_names


GIVEN_NAMES ATHLETE_NAME
Helene CORTIN, Helene
Helene JUNKER, Helene
Helene MADISON, Helene
Helene MAYER, Helene
Hélène PREVOST, Hélène
Helene "Leni" SCHMIDT, Helene "Leni"
Marie-Helene PREMONT, Marie-Helene

7 rows selected.

GIVEN_NAMES	ATHLETE_NAME
Helene	CORTIN, Helene
Helene	JUNKER, Helene
Helene	MADISON, Helene
Helene	MAYER, Helene
Hélène	PREVOST, Hélène
Helene "Leni"	SCHMIDT, Helene "Leni"
Marie-Helene	PREMONT, Marie-Helene

Statement 9

This uses Czech language rules for sorting strings, so Hélène appears last in the list

Sort strings in Czech

select substr (    
         athlete_name, instr ( athlete_name, ',' ) + 2    
       ) collate czech as given_names,   
       athlete_name   
from   olym.olym_athletes   
where  athlete_name like '%, helene%'    
         collate binary_ai   
order  by given_names


GIVEN_NAMES ATHLETE_NAME
Helene CORTIN, Helene
Helene JUNKER, Helene
Helene MADISON, Helene
Helene MAYER, Helene
Helene "Leni" SCHMIDT, Helene "Leni"
Hélène PREVOST, Hélène

6 rows selected.

GIVEN_NAMES	ATHLETE_NAME
Helene	CORTIN, Helene
Helene	JUNKER, Helene
Helene	MADISON, Helene
Helene	MAYER, Helene
Helene "Leni"	SCHMIDT, Helene "Leni"
Hélène	PREVOST, Hélène

Statement 10

With French sorting rules, Hélène is now before Helene "Leni"

Sort strings in French

select substr (   
         athlete_name, instr ( athlete_name, ',' ) + 2   
       ) collate french as given_names,  
       athlete_name  
from   olym.olym_athletes  
where  athlete_name like '%, helene%'   
         collate binary_ai  
order  by given_names


GIVEN_NAMES ATHLETE_NAME
Helene CORTIN, Helene
Helene JUNKER, Helene
Helene MADISON, Helene
Helene MAYER, Helene
Hélène PREVOST, Hélène
Helene "Leni" SCHMIDT, Helene "Leni"

6 rows selected.

GIVEN_NAMES	ATHLETE_NAME
Helene	CORTIN, Helene
Helene	JUNKER, Helene
Helene	MADISON, Helene
Helene	MAYER, Helene
Hélène	PREVOST, Hélène
Helene "Leni"	SCHMIDT, Helene "Leni"

Statement 11

You can use NLSSORT to see what value text has in different locations. This can help you understand the sorting rules for accented characters in different languages.

View the sort key for strings in different collations

select substr (   
         athlete_name, instr ( athlete_name, ',' ) + 2, 6   
       ) as given_names,  
       nlssort (   
         substr (   
           athlete_name, instr ( athlete_name, ',' ) + 2, 6   
         ) collate french   
       ) as french_sort_value,  
       nlssort (   
         substr (   
           athlete_name, instr ( athlete_name, ',' ) + 2, 6   
         ) collate czech  
       ) as czech_sort_value  
from   olym.olym_athletes  
where  athlete_name like '%, helene%'   
         collate binary_ai  
order  by given_names


GIVEN_NAMES FRENCH_SORT_VALUE CZECH_SORT_VALUE
Helene 37284B2855280001020202020200 37284B2855280002010101010100
Helene 37284B2855280001020202020200 37284B2855280002010101010100
Helene 37284B2855280001020202020200 37284B2855280002010101010100
Helene 37284B2855280001020202020200 37284B2855280002010101010100
Helene 37284B2855280001020202020200 37284B2855280002010101010100
Hélène 37284B2855280001040206020200 37284B55280002030100E8010100

6 rows selected.

GIVEN_NAMES	FRENCH_SORT_VALUE	CZECH_SORT_VALUE
Helene	37284B2855280001020202020200	37284B2855280002010101010100
Helene	37284B2855280001020202020200	37284B2855280002010101010100
Helene	37284B2855280001020202020200	37284B2855280002010101010100
Helene	37284B2855280001020202020200	37284B2855280002010101010100
Helene	37284B2855280001020202020200	37284B2855280002010101010100
Hélène	37284B2855280001040206020200	37284B55280002030100E8010100

Statement 12

You can specify a collation for columns when creating a table. Comparisons without the collate operator will use the column's setting. So ATHLETE_NAME defaults to accent- and case-insensitive search. The table's default collation applies to any columns you add without a defined collation.

Column-level collation

create table olym_athletes (	   
  id number    
    not null,    
  athlete_name varchar2(255)    
    collate binary_ai    
    not null,    
  athlete_gender varchar2(10)    
    collate binary_ci    
    not null   
) default collation binary_ai

Table created.

Statement 13

Query the data dictionary to find collations assigned to each column and the table's default

Column collation settings

select table_name,   
       default_collation, 
       column_name,  
       collation 
from   user_tables 
join   user_tab_cols 
using  ( table_name ) 
where  table_name = 'OLYM_ATHLETES'


TABLE_NAME DEFAULT_COLLATION COLUMN_NAME COLLATION
OLYM_ATHLETES BINARY_AI ID  - 
OLYM_ATHLETES BINARY_AI ATHLETE_NAME BINARY_AI
OLYM_ATHLETES BINARY_AI ATHLETE_GENDER BINARY_CI

3 rows selected.

TABLE_NAME	DEFAULT_COLLATION	COLUMN_NAME	COLLATION
OLYM_ATHLETES	BINARY_AI	ID	-
OLYM_ATHLETES	BINARY_AI	ATHLETE_NAME	BINARY_AI
OLYM_ATHLETES	BINARY_AI	ATHLETE_GENDER	BINARY_CI

Statement 14

insert into olym_athletes 
  select * from olym.olym_athletes

21415 row(s) inserted.

Statement 15

commit

Statement processed.

Statement 16

create index athlete_name_i 
  on olym_athletes ( athlete_name )

Index created.

Statement 17

You can change an existing column's collation with alter table. This is an instant, metadata operation. But there must be no indexes or virtual columns on the column! There is in this case, so trying to change its collation raises ORA-43923

Change a column's collation

alter table olym_athletes   
  modify athlete_name collate french_ci

ORA-43923: The column cannot be modified to the specified collation.

More Details: https://docs.oracle.com/error-help/db/ora-43923

Statement 18

Remove the index so you can change a column's collation

Remove the index

drop index athlete_name_i

Index dropped.

Statement 19

After dropping the index, you can now change the collation.

alter table olym_athletes  
  modify athlete_name collate french_ci

Table altered.

Statement 20

select athlete_name from olym_athletes 
where  athlete_name like '%barry%'


ATHLETE_NAME
WEITZENBERG, Charles Barry
DAGGER, Barry Edward
DANCER, Barry
DAVIS, Barry Alan
DEMET-BARRY, Deirdre
KELLY, Barry
MAGEE, Arthur Barry
MAISTER, Barry John
BARRY, Kevin
BARRY, William Louis

10 rows selected.

ATHLETE_NAME
WEITZENBERG, Charles Barry
DAGGER, Barry Edward
DANCER, Barry
DAVIS, Barry Alan
DEMET-BARRY, Deirdre
KELLY, Barry
MAGEE, Arthur Barry
MAISTER, Barry John
BARRY, Kevin
BARRY, William Louis

Statement 21

The table's default collation applies to new columns. Existing columns retain their current collation when you change the default.

Change the table's default collation

alter table olym_athletes  
  default collation german_ci

Table altered.

Statement 22

If your application often queries a column using several different collations, you can create virtual columns for each one.

Add virtual columns with different collations

alter table olym_athletes   
  add (   
    athlete_name_french as   
      ( athlete_name collate french ),   
    athlete_name_czech as   
      ( athlete_name collate czech ),   
    athlete_name_binary_ci as   
      ( athlete_name collate binary_ci )   
  )

Table altered.

Statement 23

View new default and virtual column collations

select table_name,    
       default_collation,  
       column_name,   
       collation  
from   user_tables  
join   user_tab_cols  
using  ( table_name )  
where  table_name = 'OLYM_ATHLETES'


TABLE_NAME DEFAULT_COLLATION COLUMN_NAME COLLATION
OLYM_ATHLETES GERMAN_CI ID  - 
OLYM_ATHLETES GERMAN_CI ATHLETE_NAME FRENCH_CI
OLYM_ATHLETES GERMAN_CI ATHLETE_GENDER BINARY_CI
OLYM_ATHLETES GERMAN_CI ATHLETE_NAME_FRENCH FRENCH
OLYM_ATHLETES GERMAN_CI ATHLETE_NAME_CZECH CZECH
OLYM_ATHLETES GERMAN_CI ATHLETE_NAME_BINARY_CI BINARY_CI

6 rows selected.

TABLE_NAME	DEFAULT_COLLATION	COLUMN_NAME	COLLATION
OLYM_ATHLETES	GERMAN_CI	ID	-
OLYM_ATHLETES	GERMAN_CI	ATHLETE_NAME	FRENCH_CI
OLYM_ATHLETES	GERMAN_CI	ATHLETE_GENDER	BINARY_CI
OLYM_ATHLETES	GERMAN_CI	ATHLETE_NAME_FRENCH	FRENCH
OLYM_ATHLETES	GERMAN_CI	ATHLETE_NAME_CZECH	CZECH
OLYM_ATHLETES	GERMAN_CI	ATHLETE_NAME_BINARY_CI	BINARY_CI

Statement 24

The NLS_COMP and NLS_SORT parameters control the comparison rules for columns with USING_NLS* collations. Setting these was one way to enable case- and accent- insensitive searches before Oracle Database 12.2

Check the session's current settings

select *   
from   nls_session_parameters   
where  parameter in ( 'NLS_COMP', 'NLS_SORT' )


PARAMETER VALUE
NLS_SORT BINARY
NLS_COMP BINARY

2 rows selected.

PARAMETER	VALUE
NLS_SORT	BINARY
NLS_COMP	BINARY

Statement 25

NLS_SORT is a session-modifiable parameter, so you can change this with alter session. This sets it to accent- and case-insensitive search

Change NLS_SORT

alter session set nls_sort = binary_ai

Statement processed.

Statement 26

As with NLS_SORT, use alter session to change this. How this affects queries depends on the NLS_SORT setting and the column's collation as shown in the next examples

Change NLS_COMP

alter session set nls_comp = linguistic

Statement processed.

Statement 27

To change the collation of a column, you must remove any virtual columns that use it as well as any indexes.

Remove virtual columns

alter table olym_athletes   
  drop (   
    athlete_name_french,   
    athlete_name_czech,   
    athlete_name_binary_ci  
  )

Table altered.

Statement 28

When a column has the collation as USING_NLS_COMP, queries will use the NLS_COMP & NLS_SORT values to determine the comparison rules.

Set a column to use session NLS settings

alter table olym_athletes  
  modify athlete_name collate using_nls_comp

Table altered.

Statement 29

With NLS_COMP set to LINGUISTIC, the queries inspect the value of NLS_SORT to determine the collation rules. Because the column has USING_NLS_COMP, when NLS_COMP = BINARY, the value of NLS_SORT is ignored and comparisons use exact matching

Changing comparison rules with session settings

begin 
  execute immediate 'alter session set nls_sort = binary_ci'; 
  execute immediate 'alter session set nls_comp = linguistic'; 
   
  dbms_output.put_line ( 'Case-insensitive search' ); 
  for rws in ( 
    select *  
    from   olym_athletes  
    where  athlete_name like '%, helene%'   
  ) loop 
    dbms_output.put_line ( rws.athlete_name ); 
  end loop; 
   
  execute immediate 'alter session set nls_sort = binary_ai'; 
 
  dbms_output.put_line ( 'Accent- and case-insensitive search' ); 
  for rws in ( 
    select *  
    from   olym_athletes  
    where  athlete_name like '%, helene%'   
  ) loop 
    dbms_output.put_line ( rws.athlete_name ); 
  end loop; 
   
  execute immediate 'alter session set nls_comp = binary'; 
 
  dbms_output.put_line ( 'Exact match' ); 
   
  for rws in ( 
    select *  
    from   olym_athletes  
    where  athlete_name like '%, helene%'   
  ) loop 
    dbms_output.put_line ( rws.athlete_name ); 
  end loop; 
 
   
end;

Statement processed.
Case-insensitive search
CORTIN, Helene
JUNKER, Helene
MADISON, Helene
MAYER, Helene
SCHMIDT, Helene "Leni"
Accent- and case-insensitive search
CORTIN, Helene
JUNKER, Helene
MADISON, Helene
MAYER, Helene
PREVOST, Hélène
SCHMIDT, Helene "Leni"
Exact match

Statement 30

Changing the column to USING_NLS_SORT means the database ignores the setting for NLS_COMP.

Set a column to use NLS_SORT session settings

alter table olym_athletes 
  modify athlete_name collate using_nls_sort

Table altered.

Statement 31

With the column set to USING_NLS_SORT, changing the value of NLS_SORT affects the comparison rules, even if NLS_COMP is BINARY

Changing comparison rules using NLS_SORT

begin 
  execute immediate 'alter session set nls_sort = binary_ai'; 
  execute immediate 'alter session set nls_comp = binary'; 
   
  dbms_output.put_line ( 'Accent- and case-insensitive search' ); 
  for rws in ( 
    select *  
    from   olym_athletes  
    where  athlete_name like '%, helene%'   
  ) loop 
    dbms_output.put_line ( rws.athlete_name ); 
  end loop; 
    
end;

Statement processed.
Accent- and case-insensitive search
CORTIN, Helene
JUNKER, Helene
MADISON, Helene
MAYER, Helene
PREVOST, Hélène
SCHMIDT, Helene "Leni"

Statement 32

Set a column to use the accent insenstive version of NLS_SORT

alter table olym_athletes 
  modify athlete_name collate using_nls_sort_ai

Table altered.

Statement 33

Now the column is USING_NLS_SORT_AI, all comparisons will use the accent- (and case-) insensitive version of the NLS_SORT collation. So even though this sets NLS_COMP & NLS_SORT to BINARY, the query uses accent insensitive search.

Accent-insensitive search of NLS_SORT collation

begin 
  execute immediate 'alter session set nls_sort = binary'; 
  execute immediate 'alter session set nls_comp = binary'; 
   
  dbms_output.put_line ( 'Accent-insensitive search' ); 
  for rws in ( 
    select *  
    from   olym_athletes  
    where  athlete_name like '%, helene%'   
  ) loop 
    dbms_output.put_line ( rws.athlete_name ); 
  end loop; 
    
end;

Statement processed.
Accent-insensitive search
CORTIN, Helene
JUNKER, Helene
MADISON, Helene
MAYER, Helene
PREVOST, Hélène
SCHMIDT, Helene "Leni"

Statement 34

To index queries using case-insensitive search via NLS_COMP & NLS_SORT, you need to create a function-based index using NLSSORT. The value for the second parameter must must the value you set NLS_SORT to.

Create a linguistic index

create index alth_name_nls   
  on olym_athletes (    
    nlssort ( athlete_name, 'nls_sort = binary_ai')    
  )

Index created.

Statement 35

Queries using the collate operator can also use the NLSSORT index in the previous example. You can also use collate in the index itself - this is clearer than the previous solution!

Create a linguistic index using collate

create index athlete_name_ci 
  on olym_athletes (  
    athlete_name  
      collate binary_ci  
  )

Index created.

Statement 36

select *  
from   olym_athletes  
where  athlete_name = 'latynina, larisa'  
         collate binary_ci


ID ATHLETE_NAME ATHLETE_GENDER
10549 LATYNINA, Larisa Women

ID	ATHLETE_NAME	ATHLETE_GENDER
10549	LATYNINA, Larisa	Women

Statement 37

The previous query can use the index over ATHLETE_NAME COLLATE BINARY_CI

Get the execution plan

select *  
from  dbms_xplan.display_cursor ( sql_id => '49d0cd7mkrq59', format => 'BASIC LAST' )


PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select *    from   olym_athletes    where  athlete_name = 'latynina, 
larisa'             collate binary_ci
 
Plan hash value: 3896324904
 
---------------------------------------------------------------
| Id  | Operation                           | Name            |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_ATHLETES   |
|   2 |   INDEX RANGE SCAN                  | ATHLETE_NAME_CI |
---------------------------------------------------------------
 

15 rows selected.

PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from olym_athletes where athlete_name = 'latynina,
larisa' collate binary_ci

Plan hash value: 3896324904

---------------------------------------------------------------
\| Id \| Operation \| Name \|
---------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_ATHLETES \|
\| 2 \| INDEX RANGE SCAN \| ATHLETE_NAME_CI \|
---------------------------------------------------------------

Statement 38

Collation keys are raw values. In general they are longer than the source data. When collating long strings, the key is longer than the raw limit. If you have enabled extended data types (as on Live SQL), statements will raise ORA-12742 errors when this happens. If max_string_size is still standard, the database will silently ignore characters beyond the limit.

Collating long strings

select *  
from   olym_athletes  
where  rpad ( athlete_name, 30000, 'trailing chars' ) =  
         rpad ( 'LATYNINA, Larisa', 30000, 'trailing chars' )  
         || 'extra chars' 
         collate french_ai

ORA-12742: unable to create the collation key

More Details: https://docs.oracle.com/error-help/db/ora-12742

Case- and accent-insensitive searching and sorting examples

Additional Information