0

I need to create a WHERE-IN query (using Oracle) that is case insensitive. I've tried this way:

select user from users where lower(user) in lower('userNaMe1', 'useRNAmE2');

but I get ORA-00909: invalid number of arguments

The list is dynamically generated in my Spring app. That's why I can't add lower() to every single list's value. Is there any other way to achieve it?

Ula
  • 85
  • 7

2 Answers2

3

lower() takes a single argument, so you can use:

where lower(user) in (lower('userNaMe1'), lower('useRNAmE2'))

You could also express this using regular expressions (regexp_like() accepts a case sensitivity argument) if you prefer:

where regexp_like(user, '^(userNaMe1|useRNAmE2)$', 'i')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The list is dynamically generated in my Spring app. That's why I can't add lower() to every single list's value. – Ula Aug 25 '21 at 11:16
  • 1
    But you can save your List in a Variable and add LOWER to every single String or you can use JPA instead of SQL Query. – TimLer Aug 25 '21 at 11:33
  • Thanks, you pointed me to find and use JPA method findByUserInIgnoreCase(List users) – Ula Aug 25 '21 at 13:35
0

There is another more drastic approach, and is to make your session or your searching in the database case insensitive.

You can find how to do it in this answer:

Case insensitive searching in Oracle

Aitor
  • 3,309
  • 2
  • 27
  • 32