Problem
LIKE and the other comparison operators, such as =, are case-sensitive by default.
Is there any way to make them case-insensitive?
Asked by sergionni
Solution #1
Without using full-text indexes, there are three major techniques to do a case-insensitive search in Oracle.
In the end, the strategy you use will be determined by your unique circumstances; the most important thing to remember is that you must index correctly for case-insensitive searches to increase performance.
Using UPPER() or LOWER(), you can compel all of your data to be in the same case:
select * from my_table where upper(column_1) = upper('my_string');
or
select * from my_table where lower(column_1) = lower('my_string');
If column 1 is not indexed on upper(column 1) or lower(column 1), a full table scan may be required. You can circumvent this by using a function-based index.
create index my_index on my_table ( lower(column_1) );
You must concatenate a percent around the string you’re looking for if you’re using LIKE.
select * from my_table where lower(column_1) LIKE lower('my_string') || '%';
This SQL Fiddle explains how all of these queries work. Keep an eye out for the Explain Plans, which show when an index is utilized and when it isn’t.
REGEXP LIKE() is accessible starting with Oracle 10g. In order to execute case-insensitive searching, you can use the _match parameter_ I
You must specify the start and end of the string, which are signified by the carat and the dollar sign, in order to use this as an equality operator.
select * from my_table where regexp_like(column_1, '^my_string$', 'i');
These can be eliminated to perform the same function as LIKE.
select * from my_table where regexp_like(column_1, 'my_string', 'i');
Be cautious, as your string may contain characters that the regular expression engine will read differently.
The same example output is shown in this SQL Fiddle, but this time with REGEXP LIKE ().
The NLS SORT option controls the ordering collation sequence as well as the comparison operators = and LIKE. By changing the session, you can define a binary, case-insensitive sort. This means that any queries run during that session will use case-insensitive arguments.
alter session set nls_sort=BINARY_CI
If you want to choose a different language or execute an accent-insensitive search using BINARY AI, there’s plenty of further information about linguistic sorting and string searching.
You’ll also need to update the NLS COMP option, which is as follows:
NLS COMP is set to BINARY by default, however LINGUISTIC specifies that Oracle should pay attention to the value of NLS SORT:
As a result, you’ll need to change the session once more.
alter session set nls_comp=LINGUISTIC
To boost performance, you may want to establish a linguistic index, as stated in the manual.
create index my_linguistc_index on my_table
(NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));
Answered by Ben
Solution #2
Setting the NLS COMP and NLS SORT session options in Oracle 10gR2 allows you to fine-tune the behavior of string comparisons:
SQL> SET HEADING OFF
SQL> SELECT *
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');
NLS_SORT
BINARY
NLS_COMP
BINARY
SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
2 FROM DUAL;
0
SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
Session altered.
SQL>
SQL> SELECT *
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');
NLS_SORT
BINARY_CI
NLS_COMP
LINGUISTIC
SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
2 FROM DUAL;
1
You can also make case-insensitive indexes by following these steps:
create index
nlsci1_gen_person
on
MY_PERSON
(NLSSORT
(PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
)
;
This data was gathered using Oracle case-insensitive searches. The author suggests REGEXP LIKE, however it appears to function just as well with good old =.
It can’t actually be done in versions older than 10gR2, therefore if you don’t need accent-insensitive search, just UPPER() both the column and the search query.
Answered by Álvaro González
Solution #3
Perhaps you could use
SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'
Answered by V4Vendetta
Solution #4
You can use the COLLATE operator in Oracle 12c R2:
Demo:
CREATE TABLE tab1(i INT PRIMARY KEY, name VARCHAR2(100));
INSERT INTO tab1(i, name) VALUES (1, 'John');
INSERT INTO tab1(i, name) VALUES (2, 'Joe');
INSERT INTO tab1(i, name) VALUES (3, 'Billy');
--========================================================================--
SELECT /*csv*/ *
FROM tab1
WHERE name = 'jOHN' ;
-- no rows selected
SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI = 'jOHN' ;
/*
"I","NAME"
1,"John"
*/
SELECT /*csv*/ *
FROM tab1
WHERE name LIKE 'j%';
-- no rows selected
SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI LIKE 'j%';
/*
"I","NAME"
1,"John"
2,"Joe"
*/
db<>fiddle demo
Answered by Lukasz Szozda
Solution #5
select user_name
from my_table
where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')
Answered by Clodoaldo Neto
Post is based on https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle