Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-03-14
11
Medium Priority
?
446 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
11 Comments
 
LVL 3

Accepted Solution

by:
CarlsbergFTW earned 2000 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
Independent Software Vendors: 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!

 
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 61

Expert Comment

by:HainKurt
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

572 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