CODEWITHSUNDEEP
sqloracle-databasesql-liketextmatching
user41048
user41048

Reputation: 53

How to use LIKE in a query to find multiple words?

I have a cust table 

id  name           class    mark

1   John Deo Matt   Four    75
2   Max Ruin        Three   85
3   Arnold          Three   55
4   Krish Star HN   Four    60
5   John Mike       Four    60
6   Alex John       Four    55

I would like to search for a customer which might be given as John Matt without the deo string. How to use a LIKE condition for this?

SELECT * FROM cust WHERE name LIKE '%John Matt%'

The result should fetch the row 1.

what if the search string is Matt Deo or john

The above can't be implemented when trying to find an exact name. How can I make the LIKE query to fetch the customer even if 2 strings are given?

Upvotes: 0

Views: 11874

Answers (5)

Gary_W
Gary_W

Reputation: 10360

I believe you need REGEXP_LIKE( ):

SQL> with tbl(name) as (
      select 'John Deo Matt' from dual
    )
    select name
    from tbl
    where regexp_like(name, 'matt|deo', 'i');

NAME
-------------
John Deo Matt

SQL>

Here the regex string specifies name contains 'matt' OR 'deo' and the 'i' means its case-insensitive. The order of the names does not matter.

Upvotes: 0

LoztInSpace
LoztInSpace

Reputation: 5697

Must you use LIKE? Oracle has plenty of more powerful search options.

http://docs.oracle.com/cd/B19306_01/server.102/b14220/content.htm#sthref2643

I'd look at those.

Upvotes: 0

diziaq
diziaq

Reputation: 7795

 SELECT *
   FROM custtable
  WHERE upper(NAME) LIKE '%' || upper(:first_word) || '%'
    AND upper(NAME) LIKE '%' || upper(:second_word) || '%'

Upvotes: 0

Utsav
Utsav

Reputation: 8093

Why not this

select * from cust where name Like 'John%Matt' ;

Upvotes: 1

Rene
Rene

Reputation: 10541

If the pattern to be matched is 

string1<space>anything<space>string2

you can write:

like string1||' % '||string2

Upvotes: 1

Related Questions