SQL for Mixed Case filter

Question

I was just wondering how you would search for any mix of case. For instance, I want to find all employees with the last name 'davies', but I want to be able to find any mix of case such as 'DavIes' or 'DAVies'. This is what I've tried.

SELECT LAST_NAME

FROM EMPLOYEES

WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID

FROM EMPLOYEES

WHERE LAST_NAME = '[Dd][Aa][Vv][Ii][Ee][Ss]');

I'd write a function based index which UPPER or LOWERs the name. This way the database can use the function based index to search instead of just the last_name. [Docs](https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS257) — xQbert, Oct 11 '17 at 20:18
Possible duplicate of [Case insensitive searching in Oracle](https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle) — xQbert, Oct 11 '17 at 20:21

Radim Bača · Accepted Answer · 2017-10-11T20:21:19.607

1

I would use UPPER or LOWER function and IN

SELECT LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
   SELECT DEPARTMENT_ID
   FROM EMPLOYEES
   WHERE UPPER(LAST_NAME) = 'DAVIES'
);

Unfortunately it invalidates the index use.

edited Oct 11 '17 at 20:21

answered Oct 11 '17 at 20:12

Radim Bača

10,646
1
19
33

why do you need IN? you are looking for last_name from Employees – RoMEoMusTDiE Oct 11 '17 at 21:10
@maSTAShuFu yes, it is wierd. I did not noticed that he is querying the same table in the subquery. I wrongly assumed that it must be a different table. – Radim Bača Oct 11 '17 at 21:31

score 1 · Answer 2 · answered Oct 11 '17 at 21:12

1

Using UPPER

SELECT *
   FROM EMPLOYEES
   WHERE UPPER(LAST_NAME) = 'DAVIES'

answered Oct 11 '17 at 21:12

RoMEoMusTDiE

4,739
1
17
26

SQL for Mixed Case filter

2 Answers2