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

Question

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?

% is a wildcard. so where name like 'John%Matt' will match 'John something Matt' — Rene, Oct 21 '15 at 08:50
`select * from cust where name Like '%John%Matt%' ;` And don't forget that Oracle will check the words case (case sensitive). — Hotdin Gurning, Oct 21 '15 at 10:22
Of course you can always provide examples that don't work. However, the answers given do work given your original question. Maybe you have to update your question and better specify your requirements. — Rene, Oct 21 '15 at 10:41
You will need some help from your programming language. First, extract the words into single word such as *John,Mat,Doe* then `where name like '%john%' or name like '%mat%'...`. — Hotdin Gurning, Oct 21 '15 at 10:41

score 1 · Accepted Answer · answered Oct 21 '15 at 08:52

1

If the pattern to be matched is

string1<space>anything<space>string2

you can write:

like string1||' % '||string2

answered Oct 21 '15 at 08:52

Rene

10,391
5
33
46

what if search string given `Matt Deo` – user41048 Oct 21 '15 at 09:00
See if you can rewrite my statement to suit your needs. You can always add an or ... to match string1||' '||string2. – Rene Oct 21 '15 at 09:59

score 1 · Answer 2 · answered Oct 21 '15 at 09:10

1

Why not this

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

answered Oct 21 '15 at 09:10

Utsav

7,914
2
17
38

score 0 · Answer 3 · answered Oct 21 '15 at 11:07

0

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

answered Oct 21 '15 at 11:07

diziaq

6,881
16
54
96

score 0 · Answer 4 · answered Oct 21 '15 at 12:17

0

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.

answered Oct 21 '15 at 12:17

LoztInSpace

5,584
1
15
27

score 0 · Answer 5 · answered Oct 21 '15 at 13:24

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.

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

5 Answers5