Forum Discussion

wilheim's avatar
wilheim
Copper Contributor
Jun 03, 2021

SQL Query Help - Searching With Multiple Search Terms

Hello,

 

I have a need to search a column for a dynamic number of search terms. I've been doing a bit of googling and come up with this:

SELECT * FROM Products as P Where Exists (select value from (SELECT RTRIM(LTRIM(value)) as value from string_split('25mm yellow screwdriver',' ') where value <> '') as Q Where P.Description LIKE '%'+Q.value+'%')

This works, but it returns all the rows where either 25mm, yellow or screwdriver exists in Products.Description

 

What I want though, is to only return results where 25mm and yellow and screwdriver all exist in the description. Is this possible? If so, how?

 

In my solution the search term ('25mm yellow screwdriver') will be variable, entered by the user and it could be any space delimited string with any number of words.

 

Thanks in advance.

Will.

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 04, 2021

    Next time, please format your SQL code more readable, instead of one long line.

    One possible solution is to coun the occurance of the search words. Example:

     

    declare @prod as table (id int identity(1,1), descr varchar(100));
insert INTO @prod (descr) 
values ('Yellow'), ('screwdriver'), ('25mm yellow screwdriver'), ('25mm screwdriver, yellow');

declare @search varchar(100) = '25mm yellow screwdriver';

with cte as
    (select p.descr, 
            count(*) as cnt, 
            (select count(*) from string_split(@search, ' ')) as target
     from @prod as P
          cross apply
          string_split('25mm yellow screwdriver', ' ') as s
     where p.descr like '%' + s.value + '%'
     group by p.descr)
select *
from cte
where cte.cnt = cte.target

     

    Olaf

Resources