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
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.
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.
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.
SELECT LEAST ('ROGER FEDERER','roger federer') FROM dual;
roger federer
Finally, we will call the pl/sql block above with out applying the UPPER and LOWER functions to compare the strings.
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.