I need to write an 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 unput 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?