How to perform case-insensitive search in Oracle?

Problem:

You want to perform case-insensitive search in Oracle.

Solution

One way to deal with case issues is to use the built in UPPER and LOWER functions. These functions let you force case conversion on a string for a single operation

Example

DECLARE
   full_name1  VARCHAR2(30) := 'roger federer';
   full_name2   VARCHAR2(30) := 'ROGER FEDERER';
BEGIN
   IF LOWER(full_name1) = LOWER(full_name2) THEN
       DBMS_OUTPUT.PUT_LINE( full_name1 || ' and ' || full_name2 || ' are the same.');
   END IF;
END;

In the above example the full_name1 and full_name2 are first converted into LOWER CASE then compared with each other resulting the output

roger federer and ROGER FEDERER are the same.

There is one disadvantage with UPPER and LOWER functions, which is performance. Any function applied on a field will degrade performance.

Starting with Oracle Database 10g Release 2 you can use the initialization parameters NLS_COMP and NLS_SORT to render all string comparisons case insensitive.

We need to set the NLS_COMP parameter to LINGUISTIC, which will tell the database to use NLS_SORT for string comparisons. Then we will set NLS_SORT to a case insensitive setting, like BINARY_CI.

By default, NLS_COMP is set to BINARY. we can use LEAST function to see if the the uppercase characters sort lower than the lowercase characters or the other way.

Example

SELECT LEAST ('ROGER FEDERER','roger federer') FROM dual;

The above SQL returns ‘ROGER FEDERER’ telling us that uppercase characters sort lower than the lowercase characters.

Now, we are going to set couple of paramters.

Set NLS_COMP to specify that a linguistic sort.
Set NLS_SORT to specify the sorting rules that we want.

Example

ALTER SESSION SET NLS_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI

After we set above settings to the session, we will call LEAST one more time to see what it returns.

Example

 SELECT LEAST ('ROGER FEDERER','roger federer') FROM dual;

Output

roger federer

Finally, we will call the pl/sql block above with out applying the UPPER and LOWER functions to compare the strings.

Example

DECLARE
   full_name1  VARCHAR2(30) := 'roger federer';
   full_name2   VARCHAR2(30) := 'ROGER FEDERER';
BEGIN
   IF full_name1 = full_name2 THEN
       DBMS_OUTPUT.PUT_LINE( full_name1 || ' and ' || full_name2 || ' are the same.');
   END IF;
END;

roger federer and ROGER FEDERER are the same.

The settings will remain untile you close the session.

Kiran P

Published on 05-Dec-2020 06:17:10

Previous Page Print Page