Solved

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

Posted on 2011-03-14
11
434 Views
Last Modified: 2012-08-13
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?


0
Comment
Question by:kravindra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 3

Accepted Solution

by:
CarlsbergFTW earned 500 total points
ID: 35129475
select * from test1 where lower(Name) like lower('Mike%') orupper(Name) like upper('mike%').
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35129479
select * from test1 where lower(Name) like lower('Mike%') or upper(Name) like upper('mike%').
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35129523
there is only need to use only one of those manipulators. any of upper or lower will do, using both is not needed.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35129540
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35129544
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
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35129587
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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 35129742
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35129752
>>> 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
 
LVL 3

Expert Comment

by:LFLFM
ID: 35130991
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
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35179416
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35179614
your example code shows using both at the same time, hence the comments

but no problem
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question