SQL Full Text Search

Question

Hello,
First, I apologize for my bad English.

I use LIKE in search query.
Have two columns that I search at the same time. I combine the two columns and then search them.

Example:
WHERE (Brand + ' ' + Product LIKE '%SomeBrand SomeProduct%')

I want to improve my search using Full Text Queries (CONTAINS and FREETEXT ).

The question is what is the identical (equal) replacement for Like in Full Text Queries?

I want to first join two columns and then search them with one query.
I don't want to search one column after another separately.

Thank you.

Answer 1

Hi @Milan
Agree with Ronen, to use CONTAINS, you need first create FULLTEXT catalog and you need a unique full-text index on the searched column.
How about using CHARINDEX for alternative, though I think it is no difference in performance with LIKE.

DECLARE @TEST TABLE (Brand VARCHAR(30), Product VARCHAR(30))  
INSERT INTO @TEST VALUES  
('Asus','ZenfoneSome'),  
('Samsung','GalaxySome'),  
('Huawei','MateSome')  
  
SELECT Brand,Product  
FROM @TEST    
WHERE CHARINDEX(Brand,'Samsung GalaxySome')>0 AND CHARINDEX(Product,'Samsung GalaxySome')>0  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

I want to improve my search using Full Text Queries (CONTAINS and FREETEXT ).

Do not think about these two filters as a replacement one to the other! This is a common mistake!

Each of these search options has a completely different purpose and performance can vary depending on the search and the data.

A filter of "LIKE" is not the same as a filter of "FULL TEXT SEARCH". Full-text queries perform linguistic searches against text data and the "LIKE" Transact-SQL predicate works on character patterns.

Note that FTS based on predefined FULLTEXT CATALOG and INDEX, which can cost a lot of space on the disk.

Regarding performance, FTS can improve performance of searching dramatically in some cases, but can also do the opposite mainly s the data grow. A FTS using CONTAINS doing a full text search across all of the rows in the table while using LIKE the rest of your WHERE clause can be used to reduce the result set first.

You should not check this blog before you choose your road:

https://www.brentozar.com/archive/2020/11/why-full-texts-contains-queries-are-so-slow/

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin

Answer 3

Column 1 | Column 2

Brand | Product

-------------------------

Asus | ZenfoneSome
Samsung | GalaxySome
Huawei | MateSome

User search "Samsung GalaxySome"
This is the closest example I could find for you to understand.
(I have a problem with the styles on the site.)

Answer 4

The first question is what your data looks like.

Full-text is intended for search in free text, like for instance a product description.

The search pattern you have is a little odd. You want find to find rows where Brand ends in SomeBrand and Product starts with SomeProduct. Those of conditions are a little difficult to achieve with full-text, because the idea is that you are looking for the search term anywhere in the text.

Then again, it may be as simple as Viorel suggestss, and you don't need LIKE or fulltext at all.

If not, I think we need some clarification from your side.