CONTAINS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CONTAINS

Author

Topic

davef101
Starting Member

5 Posts

Posted - 2008-09-02 : 17:54:10

Hi need some help with a Stored Procedure:

(
@SearchTerm VARCHAR(4000)
)
AS

BEGIN
SET @SearchTerm = '"' + @SearchTerm + '"'

SELECT P.ProductId,
P.PartNumber,
P.Title,
P.Description,
P.SalePrice,
P.CatagoryId,
P.Active,
C.CatName,
C.Description

FROM Products P

INNER JOIN Catagories C
ON C.CatagoryId = P.CatagoryId

WHERE
CONTAINS(P.PartNumber, @SearchTerm)
OR CONTAINS(P.Description, @SearchTerm)
OR CONTAINS(P.Title, @SearchTerm)

AND (P.Active = 'True')

ORDER BY P.PartNumber, P.Title
END

it works with single words: NGK, BPR6ES etc

but if you type: NGK BPR6ES as one search query, it returns with nothing!

Any ideas?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-02 : 18:01:59

It is working as expected and documented in SQL Server 2005 Books Online.

If you want to search for any of a list of items, your search term would have to be like this:
"NGK" OR "BPR6ES"

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources