How to find out a string which is any format using LIKe statement in PL/SQL?

I have a field name in my table. It contain a value like this "Mike".

suppose the user will search by name like this

"miKe","mIKe","MIKE". what ever possible cases. I want to display the message wheather this name  is exist or not in  the table.

select * from test1 where Name like lower('Mike%') or Name like upper('mike%').

but this query is not returning the value. what is wrong in it and how to display the valve?


kravindraAsked:
Who is Participating?
 
CarlsbergFTWCommented:
select * from test1 where lower(Name) like lower('Mike%') orupper(Name) like upper('mike%').
0
 
CarlsbergFTWCommented:
select * from test1 where lower(Name) like lower('Mike%') or upper(Name) like upper('mike%').
0
 
CarlsbergFTWCommented:
there is only need to use only one of those manipulators. any of upper or lower will do, using both is not needed.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sdstuberCommented:
select * from test1 where lower(name) like 'mike%'

or,  if mike might appear somewhere in the middle of the text

select * from test1 where lower name like '%mike%'

note, I removed the lower around the name literal since it's already known  if it will be a variable then use

lower('mike%')
0
 
sdstuberCommented:
there is no need to check both lower and upper conversions, check one or the other

also note you could use case-insensitive regular expressions


select * from test1 where regexp_like(name,'mike','i')
0
 
CarlsbergFTWCommented:
i prefer to check both for upper and lower conversion in case i'm using parameters and be fail-safe and sometimes our keyboards might fail to understand us and typos may slip inside the code ;)
0
 
HainKurtSr. System AnalystCommented:
contains
select * from myTable where lower(name) like lower(p_name)||'%'

start with
select * from myTable where lower(name) like '%'||lower(p_name)||'%'

where p_name is the parameter you pass to this pl/sql sub or function...
0
 
sdstuberCommented:
>>> i prefer to check both for upper and lower conversion

how could it possibly help?  typos or otherwise


if   upper(somecolumn) like upper('somevalue%')  is true

then lower(somecolumn) like lower('somevalue%') will also be true,

and if the upper is false so will the lower

check both will offer no additional functionality or validation
0
 
LFLFMCommented:
When you are working with case insensitive data, you need to use UPPER() or LOWER() on both sides of the comparisson: (sdstuber is corret, no need to check lower AND upper, just one OR the other):

UPPER(field1) like UPPER('%string_to_find%')   ---> case insensitive OK
field1 like UPPER('%string_to_find%') ---> partially case sensitive (this assumes field1 is always uppercase and that the string may not be)
UPPER(field1) like '%string_to_find%' ---> partially case sensitive (this assumes field1 may not be uppercase, but the string is assumed to always be uppercase)

checking both upper and lower is waste of processing and time:
UPPER(field1) like UPPER('%string_to_find%')  or LOWER(field1) like LOWER('%string_to_find%')
DONT do this!
0
 
CarlsbergFTWCommented:
there's a confusion here, i was talking about the both sides of the = (equal sign) not that i use both upper and lower for the same data at the same time.
0
 
sdstuberCommented:
your example code shows using both at the same time, hence the comments

but no problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.