Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

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
?
443 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 60

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] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

596 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