How to perform case insensitive searches

If you want to search for the following words:

‘Hello’,’hello’,’HEllo’

then you may think about using UPPER or LOWER, but there is an easier way.

All you have to do is create an index on your column you want to search that is binary based.

Here is the index

create index t_idx on t(nlssort (data, ‘NLS_SORT=BINARY_CI’));

t = table name
data = column name

Now you can perform:

select * FROM T WHERE DATA = ‘Hello’;

You will now get all permutations of ‘hello’ if the data is in your table.