MSSQL CONTAINS what am I missing here? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

MSSQL CONTAINS what am I missing here?

Author

Topic

morganeason
Starting Member

4 Posts

Posted - 2007-06-05 : 13:30:27

The record I'm trying to get back looks like

mfr_prod_id, sku, group_id
K.12.VB.52.700A26 12.VB.52.700A26, 950131, 159893

Why wont this query return this record?
SELECT group_id, MAX(sku) AS sku FROM search_part sp WHERE CONTAINS(mfr_prod_id, '"*K.12.VB.52.700A26*"') GROUP BY group_id

If I take the K. out of the contains it will match it. Any ideas?

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-06-05 : 13:59:50

Did you try removing the * at the front? BOL talks about only <prefix_term> which requires that the search phrase should be something like "Search*" and not "*Search*".

morganeason
Starting Member

4 Posts

Posted - 2007-06-05 : 14:04:58

Ya I tried it would out the leading *. Still wont match for some reason. It's like it doesn't see the first part for the mfr_prod_id.

If I run
SELECT group_id, MAX(sku) AS sku FROM search_part sp WHERE CONTAINS(mfr_prod_id, '"*12.VB.52.700A26*"') GROUP BY group_id

Notice the "K." is missing. The it returns the record.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-06 : 07:57:09

To quote BOL:

quote:
You can use the CONTAINS predicate to search a database for a specific phrase. Of course, such a query can be written using the LIKE predicate. However, many forms of CONTAINS provide far more text query capabilities than can be obtained with LIKE. Additionally, unlike using the LIKE predicate, a CONTAINS search is always case insensitive.

So here it is as a LIKE clause:


SELECT 
	group_id, MAX(sku) AS sku 
FROM 
	search_part sp 
WHERE 
	mfr_prod_id LIKE '%K.12.VB.52.700A26%' 
GROUP BY 
	group_id

Of course, with the wildcard in the front, it will not use an index if it is available.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources