how do I force Oracle DB to provide results?

Question

I have a field in my database that contains email addresses.

Some of the email addresses are lower case, some are upper case and some are mixed.

 SELECT * FROM EMPLOYEES WHERE EMAIL='john@example.com'

returns a row

 SELECT * FROM EMPLOYEES WHERE EMAIL='JOHN@example.com'

returns no rows

What can i do to always get a record back no matter what my input is? so John, JOHN, john and any combination will return his record.

I am running the query via a Stored Procedure from my C# Application. I just found out that this is Oracle 11.2g

Does this answer your question? [Case insensitive searching in Oracle](https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle) — Justin Lessard, Apr 21 '20 at 15:26
See [Oracle – Case Insensitive Sorts & Compares](https://lalitkumarb.wordpress.com/2014/01/22/oracle-case-insensitive-sorts-compares/) — Lalit Kumar B, Apr 21 '20 at 15:57

score 1 · Accepted Answer · answered Apr 21 '20 at 15:28

1

You can do something like the below

  SELECT * FROM EMPLOYEES WHERE UPPER(EMAIL)=UPPER('JOHN@example.com');

answered Apr 21 '20 at 15:28

psaraj12

And better create a function-based index to improve performance. – Lalit Kumar B Apr 21 '20 at 15:55

1 Answers1