[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

Oracle Case Insensitive Query

I'm stuck with data in an Oracle database that has the beginning letter upper case (i.e., Bob, Sally).  I can't change the format. How can I do a fuzzy, case-insensitive query on that data?

where name like UPPER('%bo%')   <- doesn't work
where name like '%Bo%'                <-  works
where name like '%bo%'                 <- doesn't work

Any ideas?  Db is Oracle 10.x
0
lcor
Asked:
lcor
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
igni7eCommented:
where upper(name)  like UPPER('%bo%')
0
 
awking00Commented:
where UPPER(name) LIKE '%BO%'
or
where LOWER(name) LIKE '%bo%'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
k_murli_krishnaCommented:
Since only first letter is upper case and rest lower case, this will be more efficient:
where LOWER(name)  like LOWER('%Bo%')
-- It can be '%bo%', '%BO%','%bO%' as well

But if the column size and data size is huge as well as number of rows then using LOWER will give more benefit over UPPER. The horizontal and vertical identification and scanning is same but less conversion is required on the column where conversion on like clause argument is only once. When conversion takes place, a function call with column value passed as IN parameter takes place and converted value is returned back.

Best will be to convert the fed in value/search string/IN parameter to have first letter as capital in which case, it will always be:
where name like '%Bo%'                <-  works
This solution will be most efficient.
0
 
igni7eCommented:
@ k muurli kriaefafaggago;m
You copy my code and awking00's code and try and steal the points.
Get a life.
0
 
awking00Commented:
You do not need the function twice. No matter how the name is structured (e.g. Bob, BOB, bob, or boB), the upper (or lower) function will turn the name into that case, so it only needs to be compared to uppercase characters (e.g. like '%BOB%') when using upper or to lowercase characters (e.g. like '%bob%') when using lower.
0
 
lcorAuthor Commented:
igni7e was solution I used but found other input helpful
0
 
k_murli_krishnaCommented:
awking00 is correct only if pattern matching data is pre-converted in application else need UPPER or LOWER 2 times especially if column as well as predicate data is mixed or opposite case.

igni7e, please do not misunderstand. Points in E-E do not matter that much. I have to take cue from what you people answered. Also, I added many points from my experience which kind of pain you people did not take. Please learn to differentiate a thief from a ordinary human being. You only got the points in the end since that is how the world is running nowadays ::)).
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now