Home » SQL & PL/SQL » SQL & PL/SQL » Like operator (Oracle 10g R2,Linux AS4)
Like operator [message #383608] Thu, 29 January 2009 05:25 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I've a SQL query like :

SQL > SELECT * FROM WVO_STORAGE_DETAILS_HIST WHERE host_name like ('%us%','%US%')

And I want the output which shows matches like USAZ as well as usdb.

I'm not able to find out even after cracking a lot .

Please help me out as its very urgent , one last time .

regards,
Raj
Re: Like operator [message #383613 is a reply to message #383608] Thu, 29 January 2009 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
200 posts, congratulations, but you should post accordingly to guidelines.

Regards
Michel
Re: Like operator [message #383615 is a reply to message #383608] Thu, 29 January 2009 05:40 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you want stuff where the host_name is like us OR like US?
(hint, hint)
Re: Like operator [message #383619 is a reply to message #383608] Thu, 29 January 2009 05:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Easy enough, you are looking for the string 'US' regardless of the case (upper/lower). I assumed that mixed case is ok too. If so, you should have a look at the following piece of code. The first option is the standard method: convert the column to upper (or lower) and compare with an uppercase (lowercase) string. The second one is just for fun. Oracle 10g has regular expressions. So you could use that as well.
-- option one:
with yourtable
as ( select 'USAZ' host_name from dual union all
     select 'UsXX' host_name from dual union all
     select 'tria' host_name from dual union all       
     select 'bGuS' host_name from dual union all
     select 'usdb' host_name from dual union all
     select 'notk' host_name from dual 
     )       
SELECT * FROM yourtable WHERE upper(host_name) like '%US%'


-- option two: regexpr_like
with yourtable
as ( select 'USAZ' host_name from dual union all
     select 'UsXX' host_name from dual union all
     select 'tria' host_name from dual union all       
     select 'bGuS' host_name from dual union all
     select 'usdb' host_name from dual union all
     select 'notk' host_name from dual 
     )       
SELECT * FROM yourtable WHERE regexp_like(host_name, '[u|U][s|S]')


Now, in case mixed case isn't allowed and the string should be either upper or lower case, here are some modified scripts:
-- option one:
with yourtable
as ( select 'USAZ' host_name from dual union all
     select 'UsXX' host_name from dual union all
     select 'tria' host_name from dual union all       
     select 'bGuS' host_name from dual union all
     select 'usdb' host_name from dual union all
     select 'notk' host_name from dual 
     )       
SELECT * FROM yourtable WHERE host_name like '%US%' OR host_name like '%us%'


-- option two: regexpr_like
with yourtable
as ( select 'USAZ' host_name from dual union all
     select 'UsXX' host_name from dual union all
     select 'tria' host_name from dual union all       
     select 'bGuS' host_name from dual union all
     select 'usdb' host_name from dual union all
     select 'notk' host_name from dual 
     )       
SELECT * FROM yourtable WHERE regexp_like(host_name, 'us|US') 


MHE
Re: Like operator [message #383875 is a reply to message #383608] Fri, 30 January 2009 20:42 Go to previous messageGo to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member

Hi

SELECT * FROM WVO_STORAGE_DETAILS_HIST WHERE host_name like ('us%')
union
SELECT * FROM WVO_STORAGE_DETAILS_HIST WHERE host_name like ('US%')

Hope this will work

[Updated on: Fri, 30 January 2009 20:45]

Report message to a moderator

Re: Like operator [message #383955 is a reply to message #383875] Sun, 01 February 2009 00:51 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sreenu80 wrote on Sat, 31 January 2009 03:42
Hi

SELECT * FROM WVO_STORAGE_DETAILS_HIST WHERE host_name like ('us%')
union
SELECT * FROM WVO_STORAGE_DETAILS_HIST WHERE host_name like ('US%')

Hope this will work

If you think this solution is better than Maahers, you should at least use union all instead of union. The first statement will never return a row that the second will return.
Previous Topic: ODCI aggregate question (merged)
Next Topic: Is my assumption correct in the correlated update query
Goto Forum:
  


Current Time: Sat Dec 10 22:13:37 CST 2016

Total time taken to generate the page: 0.06997 seconds