CODEWITHSUNDEEP
sqloracle-database
Ula
Ula

Reputation: 105

Case insensitive where-in SQL query in Oracle

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?

Upvotes: 0

Views: 350

Answers (2)

Aitor
Aitor

Reputation: 3419

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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')

Upvotes: 3

Related Questions