SQL Query Help - Searching With Multiple Search Terms

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