Case insensitive searching in Oracle
The default behaviour of
LIKE and the other comparison operators,
= etc is case-sensitive.
Is it possible make them case-insensitive?
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 create case insensitive indexes:
create index nlsci1_gen_person on MY_PERSON (NLSSORT (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI') ) ;
This information was taken from Oracle case insensitive searches. The article mentions
REGEXP_LIKE but it seems to work with good old
= as well.
In versions older than 10gR2 it can't really be done and the usual approach, if you don't need accent-insensitive search, is to just
UPPER() both the column and the search expression.
There are 3 main ways to perform a case-insensitive search in Oracle without using full-text indexes.
Ultimately what method you choose is dependent on your individual circumstances; the main thing to remember is that to improve performance you must index correctly for case-insensitive searching.
1. Case your column and your string identically.
select * from my_table where upper(column_1) = upper('my_string');
select * from my_table where lower(column_1) = lower('my_string');
column_1 is not indexed on
lower(column_1), as appropriate, this may force a full table scan. In order to avoid this you can create a function-based index.
create index my_index on my_table ( lower(column_1) );
If you're using LIKE then you have to concatenate a
% around the string you're searching for.
select * from my_table where lower(column_1) LIKE lower('my_string') || '%';
This SQL Fiddle demonstrates what happens in all these queries. Note the Explain Plans, which indicate when an index is being used and when it isn't.
2. Use regular expressions.
From Oracle 10g onwards
REGEXP_LIKE() is available. You can specify the _match_parameter_
'i', in order to perform case-insensitive searching.
In order to use this as an equality operator you must specify the start and end of the string, which is denoted by the carat and the dollar sign.
select * from my_table where regexp_like(column_1, '^my_string$', 'i');
In order to perform the equivalent of LIKE, these can be removed.
select * from my_table where regexp_like(column_1, 'my_string', 'i');
Be careful with this as your string may contain characters that will be interpreted differently by the regular expression engine.
This SQL Fiddle shows you the same example output except using REGEXP_LIKE().
3. Change it at the session level.
The NLS_SORT parameter governs the collation sequence for ordering and the various comparison operators, including
= and LIKE. You can specify a binary, case-insensitive, sort by altering the session. This will mean that every query performed in that session will perform case-insensitive parameters.
alter session set nls_sort=BINARY_CI
There's plenty of additional information around linguistic sorting and string searching if you want to specify a different language, or do an accent-insensitive search using BINARY_AI.
You will also need to change the NLS_COMP parameter; to quote:
The exact operators and query clauses that obey the NLS_SORT parameter depend on the value of the NLS_COMP parameter. If an operator or clause does not obey the NLS_SORT value, as determined by NLS_COMP, the collation used is BINARY.
The default value of NLS_COMP is BINARY; but, LINGUISTIC specifies that Oracle should pay attention to the value of NLS_SORT:
Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.
So, once again, you need to alter the session
alter session set nls_comp=LINGUISTIC
As noted in the documentation you may want to create a linguistic index to improve performance
create index my_linguistc_index on my_table (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));
Read more... Read less...
maybe you can try using
SELECT user_name FROM user_master WHERE upper(user_name) LIKE '%ME%'
From Oracle 12c R2 you could use
The COLLATE operator determines the collation for an expression. This operator enables you to override the collation that the database would have derived for the expression using standard collation derivation rules.
The COLLATE operator takes one argument, collation_name, for which you can specify a named collation or pseudo-collation. If the collation name contains a space, then you must enclose the name in double quotation marks.
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" */
select user_name from my_table where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')
you can do something like that:
where regexp_like(name, 'string$', 'i');