question about freetexttable matching   

I have a full text cat for "products" set up against a table with product 
information such as product-ID, product-name, product-description, all of 
which are in the view. All the fields are varchars.

I have two similar products with similar product-IDs, like:

1234
1234XL

If I perform a freetexttable query against the view, and search for "1234", 
only the first product is returned (and if I search for "1234XL" the second 
is returned). If I embed "1234" in the product-description for both 
products, then both products are returned.

So, it seems like my freetexttable query is looking for an exact string 
match...which I guess would make sense. But I'm wondering if there's any way 
for it to perform something closer to a "Like" query:

select * from table where product-ID like '%1234%' --which would return both 
products.

My real query looks like:

SELECT DISTINCT p.product-id, p.product-name, p.product-description, RANK 
from products p INNER JOIN (SELECT Rank, [KEY] FROM 
FREETEXTTABLE(viewSearch,*, '1234')) AS k ON k.[key]=s.sku GROUP BY k.rank, 
p.product-id, p.product-name, p.product-desc ORDER BY Rank DESC

Is there any way to modify the above query so that it would find both 
product-IDs given a partial match against both?

date: Tue, 2 Sep 2008 17:24:37 -0400   author:   geekyguy

Re: question about freetexttable matching   

I'm sorry...in my haste to strip out some extraneous stuff I made a mess of 
the query...here's something more accurate:

SELECT DISTINCT p.product-id, p.product-name, p.product-desc,
RANK from products p
Full JOIN sku s ON p.product-id = s.product-id
INNER JOIN (SELECT Rank, [KEY] FROM FREETEXTTABLE(viewSearch,*, '1234')) AS 
k
ON k.[key]=s.sku GROUP BY k.rank, p.product-id, p.product-name, 
p.product-desc
ORDER BY Rank DESC

where sku is a primary key field (i.e. unique)

"geekyguy"  wrote in message 
news:Od8eOJUDJHA.3104@TK2MSFTNGP03.phx.gbl...
>I have a full text cat for "products" set up against a table with product 
>information such as product-ID, product-name, product-description, all of 
>which are in the view. All the fields are varchars.
>
> I have two similar products with similar product-IDs, like:
>
> 1234
> 1234XL
>
> If I perform a freetexttable query against the view, and search for 
> "1234", only the first product is returned (and if I search for "1234XL" 
> the second is returned). If I embed "1234" in the product-description for 
> both products, then both products are returned.
>
> So, it seems like my freetexttable query is looking for an exact string 
> match...which I guess would make sense. But I'm wondering if there's any 
> way for it to perform something closer to a "Like" query:
>
> select * from table where product-ID like '%1234%' --which would return 
> both products.
>
> My real query looks like:
>
> SELECT DISTINCT p.product-id, p.product-name, p.product-description, RANK 
> from products p INNER JOIN (SELECT Rank, [KEY] FROM 
> FREETEXTTABLE(viewSearch,*, '1234')) AS k ON k.[key]=s.sku GROUP BY 
> k.rank, p.product-id, p.product-name, p.product-desc ORDER BY Rank DESC
>
> Is there any way to modify the above query so that it would find both 
> product-IDs given a partial match against both?

date: Tue, 2 Sep 2008 17:57:25 -0400   author:   geekyguy