CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
krishnapriya
krishnapriya

Reputation: 19

How to get case insensitive records from oracle?

How will I get all the customer_names starting from 'a' from customer table?

For this I want to get all the names which contain the letter a irrespective of case sensitivity. I don't want to use to_upper() because it will negatively impact query performance.

Upvotes: 0

Views: 638

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

If you are merely concerned about performance, you can create a function-based index

create index idx_fbi_upname
on
table( upper(name) );

select *
  from table t
 where upper(t.name) like 'A%'

This query can use the index on upper(name). Of course, if you want to do both case-sensitive and case-insensitive queries, you may end up with twice as may indexes which would involve more storage space and more work to maintain.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269447

Getting names which contain the letter "a" is probably going to require a full table scan. You can do something like this:

where name like '%a%' or name like '%A%'

or

where lower(name) like '%a%'

Under most circumstances, these will require full table scans.

If you are just looking for rows that begin with "a", then you can do:

select t.*
from table t
where t.name like 'a%'
union all
select t.*
from table t
where t.name like 'A%';

This can make use of an index on t(name). However, this still may not use an index, because such string operations may not be sufficiently selective.

Upvotes: 0

Related Questions