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?
and '%@keyword%'
– T. Peter