Sql Server query to search database with multiple keywords

Question

Sql Server query to search database with multiple keywords

henryvuong

SSC Enthusiast

Points: 172
More actions
January 6, 2021 at 2:46 am

#3827912

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 1

In the future, would you mind following the basic netiquette that's been in effect for over 30 years on SQL forums and post real DDL?

CREATE TABLE Inventory

(gtin CHAR(15) NOT NULL PRIMARY KEY,

product_name VARCHAR(25) NOT NULL);

When we created the SQL standards, we had no intention of them being used for text search. Those tools were being standardized over in the Z39 committee and not us. You're about to find out that a hammer makes a lousy screwdriver 🙂

Your solution of like predicates is probably the way to go. You just need to write the statement correctly:

SELECT * FROM inventory

WHERE product_name LIKE '% @search_word1%'

AND (product_name LIKE '% @search_word2%' OR 1=1)

AND (product_name LIKE '% @search_word3%' OR 1=1);

The OR predicates will work with anything, so you don't have to always provide a full set of search words. Since I did some work at one point with textbases, a keyword is part of a KWIC (key word in context) or other indexing system. What you have is a search word for a parameter. Picky, picky, picky.

Please post DDL and follow ANSI/ISO standards when asking for help.

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

henryvuong wrote:

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?

Don't worry, all of us except one understand that the example you provided is simply a concept, not a real example of your schema and data.

Have you looked at Lucene? https://lucene.apache.org/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

henryvuong wrote:

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?

Finish what you started and that would be to finish doing your research on the Full Text Search feature. It has a "Thesaurus" feature that will allow you to do things like equate the word "SmartPhone" with the synonym of "phone" so that you don't need to cripple the performance and resource usage of your queries by using leading wild-cards.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 4

Put your search words in another table.

SELECT  t.*
FROM    dbo.table1 AS t
WHERE   EXISTS (SELECT * FROM dbo.table2 AS x WHERE t.Product LIKE '%' + x.searchtext +'%')

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

SwePeso wrote:

Put your search words in another table.

SELECT  t.*
FROM    dbo.table1 AS t
WHERE   EXISTS (SELECT * FROM dbo.table2 AS x WHERE t.Product LIKE '%' + x.searchtext +'%')

Ah, be careful... that's going to return every row that has ANY keyword present in it rather that returning just the rows that have ALL the keywords present. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 6

Ok then. Apply some relational division.

SELECT     t.Product
FROM       dbo.table1 AS t
INNER JOIN dbo.table2 AS x ON t.Product LIKE '%' + x.searchtext +'%'
GROUP BY   t.Product
HAVING     COUNT(*) = (SELECT COUNT(*) FROM dbo.table2);

N 56°04'39.16"
E 12°55'05.25"

DesNorton SSC-Insane Points: 24278 More actions · Answer 7

Perhaps Trigram searches would help in this case

Trigram Wildcard String Search in SQL Server

How to post data/code on a forum to get the best help.