SQL how to do a LIKE search on each value of a declared variable

I have a query where I am trying to do a LIKE search on each value of a declared variable, instead of doing a like search on the entire field value/string.

Example:

    DECLARE @name VARCHAR(30)
    SET @name = 'John Smith'
    SELECT name FROM customers WHERE name like '%'+ @name + '%'

The record I am looking for is “John and Jane Smith”. The query above returns NO result. If the user searches just ‘John’ OR just ‘Smith’ there are too many results returned.

I am trying to get the query to search like the query below:

    SELECT name from customers WHERE name LIKE '%John%  %Smith%'

I’ve searched for many options but not sure if my search terms are not correct, I have yet to find a solution.

Answer

I would try replacing spaces in your @name with '% %' Something like

SET @nameFilter = REPLACE(@name,' ','% %')
SELECT name FROM customers WHERE name like '%'+ @ nameFilter + '%'