3 Replies - 5093 Views - Last Post: 29 April 2011 - 03:11 AM

#1 TechDee  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 21-May 10

LIKE condition in SQL

Posted 25 March 2011 - 11:42 AM

Hello, using Oracle 10g for class...

I have coded this to search for bob or Bob and display it in descending order.

What I have is this:

select MOVIE_NAME,MOVIE_YEAR,MOVIE_COST
FROM MOVIE
order by desc(MOVIE_NAME) like '%bob%';

I used this:
where MOVIE_NAME like '%bob%'; it displayed one finder 

My questions is how to search for combinations such as bob and Bob or hope and Hope? 


Is This A Good Question/Topic? 0
  • +

Replies To: LIKE condition in SQL

#2 croog24  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 37
  • Joined: 04-February 10

Re: LIKE condition in SQL

Posted 27 March 2011 - 10:36 AM

Basically just as you said it. SQL tends to be a very literal language.
It is a little unusual though, because I had thought SQL was not case sensitive, but either way this should answer your problem. It may be you have your database setup as case sensitive. I can double check if this works when I get home and have access to Oracle.

where MOVIE_NAME like '%bob%' AND '%Bob%';


Was This Post Helpful? 0
  • +
  • -

#3 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: LIKE condition in SQL

Posted 23 April 2011 - 04:07 AM

If you use
where lower(MOVIE_NAME) like '%bob%'
it will always work case-insensitive. The performance will be affected as a normal index cannot be used with this construction. If you're not using ueber-huge tables, it will work ok.
Was This Post Helpful? 1
  • +
  • -

#4 kpratama  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-April 11

Re: LIKE condition in SQL

Posted 29 April 2011 - 03:11 AM

View PostTechDee, on 25 March 2011 - 11:42 AM, said:

Hello, using Oracle 10g for class...

I have coded this to search for bob or Bob and display it in descending order.

What I have is this:

select MOVIE_NAME,MOVIE_YEAR,MOVIE_COST
FROM MOVIE
order by desc(MOVIE_NAME) like '%bob%';

I used this:
where MOVIE_NAME like '%bob%'; it displayed one finder 

My questions is how to search for combinations such as bob and Bob or hope and Hope? 



try this
where movie_name like '%bob%' or '%Bob%'


or keyword will return whether your movie_name have 'bob' or 'Bob'
if you use keyword and , it will return movie_name that have 'bob' and 'Bob'
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1