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.
You can force all your data to be the same case by using UPPER() or LOWER():
select * from my_table where upper(column_1) = upper('my_string');
If column_1 is not indexed on upper(column_1) or 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.
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.
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().
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.
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
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'));
"create a function-based index" Amazing what a difference this can make
Really nice answer @Ben covers all the ways i could find in multiple locations.
+1. Also in the 4th query from top, I guess to intended to write "LIKE" instead of =
select * from my_table where lower(column_1) LIKE lower('my_string%');
One reason would be if your query is paramerterized (likely in most situations) then your calling code doesn't need to always concatenate a % on the end @lopezvit.
If there are some characters that will mess-up the result of regexp_like, is there a way to escape such strings? Giving an example, if the string has $, the output will be not as what we expect. //cc @Ben and others please do share.
` is the escape character @bozzmob. There should be no difference in output if the string the regular expression is operating on contains a $, this may only cause you problems if you need a $ literal in your regular expression. If you've got a specific issue I'd ask another question if this comment/answer hasn't helped.