SQL Server query to search database with multiple keywords

Question

I need to write a SQL Server query to allow user to search a table with multiple keywords. The table may look like this:

Table t

| ID  | Product  
+-----+-------------------------------------------
| 1   | Apple iphone 4 8GB AT&T
| 2   | Apple iPhone 5 16GB Verizon
| 3   | Apple iPhone 5S 32GB Unlocked 
| 4   | Samsung Galaxy 7 32GB Unlocked Smartphone
| 5   | Motorola Moto G6 32GB Unlocked Smartphone
| 6   | Blackberry Z10 16GB Verizon Smartphone 

When user enters keywords "unlocked phone 32" it should return:

| ID  | Product 
+-----+-------------------------------------------
| 3   | Apple iPhone 5S 32GB Unlocked 
| 4   | Samsung Galaxy 7 32GB Unlocked Smartphone 
| 5   | Motorola Moto G6 32GB Unlocked Smartphone   

I could write:

SELECT *
FROM t 
WHERE Product LIKE '%@keyword1%' 
  AND '%@keyword2%' 
  AND '%@keyword3%'

Where each keyword can be a word from the user's input string, but I do not know how many keywords user may enter, and the keywords can be in any order, so the above query does not work in all cases. I am thinking of using full text search, but full text search does not allow leading wildcard, a search for "phone" will not return any record.

What can I do?

Answer 1

The approach i take is to split up the search_string and compare each one on the join condition using the like operator.

Finally i check the count of matches of the splitted string with the count of separators in the main string. If they match-> then its part of the final output.

Note you can control this and assign a percentage match as well. Eg: 2 out of 3 keywords are matched -> a.cnt_of_matches/a.cnt_of_splits

Eg:

create table dbo.test(id int, product nvarchar(100))


insert into dbo.test values(1,'Apple iphone 4 8GB AT&T')
insert into dbo.test values(2,'Apple iPhone 5 16GB Verizon')
insert into dbo.test values(3,'Apple iPhone 5S 32GB Unlocked')
insert into dbo.test values(4,'Samsung Galaxy 7 32GB Unlocked Smartphone')
insert into dbo.test values(5,'Motorola Moto G6 32GB Unlocked Smartphone')
insert into dbo.test values(6,'Blackberry Z10 16GB Verizon Smartphone')

with data
  as (select value as col1,count(*) over() as cnt_of_splits
        from string_split('unlocked phone 32',' ')
      )
    ,matched_products
     as (select *,count(*) over(partition by id) as cnt_of_matches
           from data d
           join dbo.test t
             on t.product  like concat('%',d.col1,'%')
        )
select distinct product 
  from matched_products a
where a.cnt_of_matches=a.cnt_of_splits

Apple iPhone 5S 32GB Unlocked
Motorola Moto G6 32GB Unlocked Smartphone
Samsung Galaxy 7 32GB Unlocked Smartphone