The default behaviour of LIKE and the other comparison operators,
= etc is case-sensitive.
Is it possible make them case-insensitive?
You can also create case insensitive indexes:
This information was taken from Oracle case insensitive searches. The article mentions
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
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.
If you're using LIKE then you have to concatenate a
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
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.
In order to perform the equivalent of LIKE, these can be removed.
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
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 default value of NLS_COMP is BINARY; but, LINGUISTIC specifies that Oracle should pay attention to the value of NLS_SORT:
So, once again, you need to alter the session
As noted in the documentation you may want to create a linguistic index to improve performance
maybe you can try using
Friendly reminder that some of the example searches will result in a full table scan even if there is an index on user_name.