Dealing with the lowercase text for LIKE operator

Databases
1

I am using the following SQL which works fine:

SELECT EMPLOYEE_ID,
       LOWER(NAME) AS NAME,
       CP_ID
FROM
      EMPLOYEE
WHERE CP_ID = 4
AND LOWER(NAME) LIKE '%alpha%' AND rownum < 1001
ORDER BY employee.name;

However, in the LIKE operator, when I supply %Alpha% or %ALPHA%, it doesn’t return any result. Is there a way I could convert the text between %% so that regardless of what case user enters, it will always receive small case text which could be searched for. I am using Oracle database

2

Are you using case sensitive collation? (…_cs vs. …_ci)

3

I am not sure what that is because I didn’t set up the database things. Please tell me how that is related and what do I need to check , I will check with the person who created the database. Thanks

4

I think in the application language you need to convert the value being used in the LIKE comparison to be lowercase. The query is already comparing against converting the value in the db column to lowercase. The part where is says LOWER(NAME) is converting the name to lowercase and comparing against the input of the right operand of the LIKE comparison.

5

Sorry, I was thinking MySQL, maybe Oracle doesn’t have that?

I agree with ZooKeeper that the way to do this would be to lowercase the variable before you use it in the query.

6

Okay, thanks. I will try that.

1 Like
7

No problem. Thanks

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.