Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

LIKE case insensitivity

538129Dec 11 2007 — edited Dec 11 2007
I am attempting to do a query using the LIKE keyword however I want to match all cases i.e. make the query case-insensitive. How can I achieve this please? Currently this will only match the exact case specified.

Comments

MichaelS
This?:
...where lower(col1) like 'your_value%'
605343
Or 
... where upper(col1) like 'VALUE%'
:)))
586006
What if data is in mixed character ?

eg:
YOUR_value
MichaelS
What if data is in mixed character ?
...where lower(col1) like lower('your_value%')
;)
castorp

Or

where regexp_like(col1, 'YOUR_value', 'i')
602093
Like GGS and other said you can lower case or upper case your column instead of fighting for data match in the right side of the LIKE clause:

WHERE LOWER(your_column) LIKE '%abcd%'

OR

WHERE UPPER(your_column) LIKE '%ABCD%'

Jithendra
Sven W.
Or
What if data is in mixed character ?
...where Initcap(col1) like Initcap('your_value%')
(just to throw in my 2 €-cents)
586006
Will it work for the values like this (YOUR_value )?
cd_2
The equivalent of LIKE 'your_value%' would be '^your_value' or '^YOUR_value' using the case insensitive matching parameter.

C.
BluShadow
Will it work for the values like this (YOUR_value )?
Rather than ask "will it work for" questions why not try it yourself.
Sven W.
Will it work for the values like this (YOUR_value )?
Yes. Any version will work as long as you use the same function on each side of the like operator.
cd_2
I couldn't resist and compiled all the entries so far: Search me.

C.
586006
Sorry ....I tried it and its working. Thanks for scolding me.

SQL> ed
Wrote file afiedt.buf

1* select * from employees where Initcap(last_name) like Initcap('KO_chhar%')
2 /


EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
101 Neena KO_chhar
NKOCHHAR 515.123.4568 21-SEP-89 AD_VP 17000
100 90
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 8 2008
Added on Dec 11 2007
13 comments
52,098 views