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?

Answer 1

If the pattern to be matched is

string1<space>anything<space>string2

you can write:

like string1||' % '||string2