CODEWITHSUNDEEP
sqlsql-serverfull-text-search
Jonny Vu
Jonny Vu

Reputation: 1428

Full text search in sql server

I am using SQL Server 2008. I have indexed the ProductName column in the Product table as full text search index. Some ProductName values as examples:

ProductName
-------------
Iphone 3GS
Iphone 4S
Iphone 5S
Iphone 5

Now I am using the following code to search for Product:

WHERE CONTAINS (Product.ProductName, '"Iphone 4S"')

It's OK! But if I edit and use this:

WHERE CONTAINS (Product.ProductName, '"4S Iphone"')

No results!

Could you help me to solve this problem? Thanks.

Upvotes: 8

Views: 19188

Answers (6)

Maaz Ali
Maaz Ali

Reputation: 1

DECLARE @fullTextCondition NVARCHAR(1000) = '4S Iphone'
 SET @fullTextCondition = '"' + REPLACE(@fullTextCondition, ' ', '" And "') + '*"'
SELECT * FROM Product
WHERE CONTAINS(ProductName, @fullTextCondition);

Upvotes: 0

Vasan
Vasan

Reputation: 375

Bit late to the party, nevertheless, wondering if

FreeText

will address the issue of the OP, instead of using CONTAINS with AND/OR, as FreeText takes multiple keywords to search.

Upvotes: 0

DOOMDUDEMX
DOOMDUDEMX

Reputation: 644

Individual search-phrases should be encased in quotations.

If you were to compare the results of there 2 lines you would probably find the results you desire:

WHERE CONTAINS (Product.ProductName, '"Iphone" and "4S"')

WHERE CONTAINS (Product.ProductName, '"4S" and "Iphone"')

This is because you might want to search on combinations which involve spaces:

WHERE CONTAINS (Product.ProductName, '"Sweet apple" or "Sour orange" or "Sour apple"')

In this case you would not be able to find a sweet orange for example.

The point is to not limit your search to individual words but actually allow you to require different combinations of words if you so choose.

Upvotes: 1

raykendo
raykendo

Reputation: 640

Sounds like you'll want to use the NEAR operator between 4S and Iphone. It searches for words with those, and the order can be independent.

WHERE CONTAINS (Product.ProductName, '"4S" NEAR "Iphone"')

You can also use a tilde(~) in place of the NEAR statement

WHERE CONTAINS (Product.ProductName, '"4S" ~ "Iphone"')

Upvotes: 2

MaheshMajeti
MaheshMajeti

Reputation: 187

you want find out results which contain Iphone,4S.So you can use OR condition to get the result.

WHERE CONTAINS (Product.ProductName, '4S  OR Iphone')

Following link will be more useful for better understanding. http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

Upvotes: 6

Kiril Rusev
Kiril Rusev

Reputation: 753

why dont you go with:

 product.ProductName like '%3GS%'
 product.ProductName like '%4S%'

if you want to be even more accurate then:

 where product.ProductName like '%4S%'
       and product.ProductName like '%Iphone%'

Upvotes: -5

Related Questions