Solved

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

Posted on 2011-03-14
11
415 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 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
 
LVL 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 51

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
Need help on Index -Oracle -Challenging question 5 44
Oracle RMAN Database Restore 5 32
sql query 9 22
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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now