Full-Text searching (CONTAINS) - middle of words 1

[Borvik]

I'm attempting to implement a search, the actual search uses CONTAINSTABLE - but here is an example using just CONTAINS:

SELECT * FROM _productsearch WHERE searchtext LIKE '%micro%'
SELECT * FROM _productsearch WHERE CONTAINS(searchtext, ' "*micro*" ')

The first query will return anything with "micro" in it, the second will only get words that start with "micro".

I'd really like to be able to search anywhere within the column (not just the beginning of words), with CONTAINS/CONTAINSTABLE - as I wouldn't have to refactor the entire search query (and what builds it).

Here is the search query (the group by doesn't really have vpl.*, but it trims down on the numerous columns vpl has).

WITH searchProd AS (
  SELECT 
    ROW_NUMBER() OVER(ORDER BY SUM(st.[RANK]) DESC) AS RowNum, 
    st.productid, 
    vpl.*,
    SUM(st.[RANK]) AS [search-rank] 
  FROM 
    (SELECT ps.productid, kt.RANK FROM _productsearch ps INNER JOIN CONTAINSTABLE(_productsearch, searchtext, '"*micro*"') AS kt ON ps.searchidx = kt.[KEY]) AS st LEFT OUTER JOIN 
    (SELECT * FROM v_productlisting) AS vpl ON st.productid = vpl.productid
  WHERE st.productid NOT IN ('') AND vpl.mfgpartno IS NOT NULL
  GROUP BY st.productid, vpl.*
) 
SELECT * FROM searchProd WHERE RowNum BETWEEN 1 AND 15

Any ideas on how to search columns this way?

 

[HarleyQuinn]

Why not just:

SELECT * FROM _productsearch WHERE CONTAINS(searchtext, 'micro')

That way should be using CONTAINS with a simple_term where (even though you've added a * to the beginning of the string) you are using a prefix_term, which explains the results you are returning. More information see links (they are for 2005 but if you're using a different version you should still be able to get to the documentation for that quite easily). See CONTAINS and CONTAINSTABLE

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Borvik]

I've tried that - and I am using 2005.

When I do that - I don't even get results with words that start with micro (ex. microphone). The only results I do get are ones with the word micro in the (ex. "Intel Core micro architecture").

I need to be able to accomodate multiple keywords (with the contains/containstable I know how to do this '"term" OR "term2"'), and using the example of micro, I need it to be able to pull results like: "Supermicro Computer, Inc", "1 x Mini-phone Microphone Front", and "Intel Core micro architecture".

I've seen those pages, and wasn't able to find anything relevant to my situation.