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.
Published on 05-Dec-2020 10:47:10
