Unable to use CONTAINS on single numeric value

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)

Unable to use CONTAINS on single numeric value

Author

Topic

salblz
Starting Member

3 Posts

Posted - 2009-11-14 : 08:47:44

I'm building a dynamic SQL query using .net to query a FTC on a products catalog.

If somebody searches for "Size 3", the dynamic query will generate something like:

SELECT * FROM Products WHERE CONTAINS(Title, 'size') AND CONTAINS(Title, '3')

The query above will return 0 results because of the "3" value. If I change to title to "Size 34" it will display the valid results. It seems to be excluding any results from a single numeric value < 10. The following SQL will work:

SELECT * FROM Products WHERE CONTAINS(Title, 'size') AND CONTAINS(Title, '34')

Any Ideas?

BTW, I'm using SQL 2008 Professional.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-14 : 08:57:44

See this example there is an asterix in use. Maybe that's the solution for you?
C. Using CONTAINS
The following example returns all product names with at least one word starting with the prefix chain in the Name column.

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO
Source: http://msdn.microsoft.com/en-us/library/ms187787.aspx

Why do you post in forum for 2005 when you are using 2008?

No, you're never too old to Yak'n'Roll if you're too young to die.

salblz
Starting Member

3 Posts

Posted - 2009-11-14 : 09:39:35

Unfortunatley, that's not a solution. Thanks for the reply. I apologize for posting in the wrong forum.

Does anybody have any insight why a double digit such as "34" will work, but on a single digit (ie: "3") it won't?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-16 : 08:48:42

From the link Fred gave you: How to properly use the AND operator with CONTAINS:

I. Using CONTAINS with A Logical Operator (AND)

The following example uses the ProductDescription table of the AdventureWorks database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word "Aluminum" and the word "spindle." The search condition uses the AND Boolean operator.

USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, ' Aluminum AND spindle');
GO


GO

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-16 : 18:50:40

Is it possible that the '3' is being interpreted as a numeric value? What would happen if you were to explicitly CAST the value to a varchar?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources