Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies'; var adpushup = adpushup || {}; adpushup.que = adpushup.que || []; adpushup.que.push(function() { adpushup.triggerAd(ad_id); });
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=LINGUISTICALTER 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.
Updated on: 05-Dec-2020
3K+ Views
Related Articles
- Case insensitive search in Mongo?
- How to achieve case sensitive uniqueness and case insensitive search in MySQL?
- How to perform Case Insensitive matching with JavaScript RegExp?
- Perform case insensitive SELECT using MySQL IN()?
- MongoDB query with case insensitive search?
- MongoDB query for specific case insensitive search
- How can I search (case-insensitive) in a column using LIKE wildcard?
- How to use case-insensitive switch-case in JavaScript?
- MongoDB $regex operator i or I for case insensitive search
- How to perform string aggregation/concatenation in Oracle?
- How to make a case-insensitive query in MongoDB?
- Case-insensitive Dictionary in C#
- How to make jQuery attribute selector case insensitive?
- MySQL case-insensitive DISTINCT?
- Is Python case-sensitive or case-insensitive?
Advertisem*nts
';adpushup.triggerAd(ad_id); });
