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.
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.
I would try replacing spaces in your
SET @nameFilter = REPLACE(@name,' ','% %') SELECT name FROM customers WHERE name like '%'+ @ nameFilter + '%'