April 21, 2004 at 7:22 pm
The Microsoft SQL full-text indexing seems
to be working fine, however why does it only
work when I insert a value and it fails to
work when I insert the @txt varaible like
the original StoredProc below ?
Replaced "name like '%' + @txt + '%'" with
1. FREETEXT(NAME, 'ginger') --> works
2. FREETEXT(NAME, 'txt') --> fails
3. FREETEXT(NAME, @txt) --> fails
4. FREETEXT(NAME, '@txt') --> fails
------------------------------------------
** Originial **
CREATE PROCEDURE p_SEARCH_PRODUCTS
@TXT VARCHAR(500)
AS
DECLARE @today DECIMAL
set @today = DBO.DATETOMS(GETDATE())
SELECT
0 CATEGORY_ID,
P.PRODUCT_ID,
0 CAT_COUNT,
P.NAME,
P.SHORT_DESC,
P.THUMBNAIL,
MIN(S.PRICE) PRICE,
P.NAME_EXT,
0 PACKAGE_FLAG
INTO #OUT
FROM
PRODUCT P,
SKU S
WHERE
P.PRODUCT_ID IN (
select PRODUCT_ID from product where
name like '%' + @txt + '%'
)
AND P.PRODUCT_ID=S.PRODUCT_ID
AND @today between P.DATE_ACTIVATE AND P.DATE_DEACTIVATE
AND P.ACTIVE = 1 AND (P.DELETE_FLAG = 0 OR P.DELETE_FLAG IS NULL)
GROUP BY P.PRODUCT_ID,P.NAME,P.SHORT_DESC,P.THUMBNAIL,P.NAME_EXT
UPDATE #OUT SET PACKAGE_FLAG = 1 WHERE PRODUCT_ID IN (
SELECT PRODUCT_GROUP_PRODUCT_ID FROM PRODUCT_GROUP G WHERE
PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
) -- 1 IS QUALITIES USED FOR PACKAGE OPTIONS
OR PRODUCT_ID IN(
SELECT PRODUCT_ID FROM PRODUCT_GROUP G WHERE PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
AND PRODUCT_ID <> PRODUCT_GROUP_PRODUCT_ID
)
SELECT top 100 * FROM #OUT
GO
April 23, 2004 at 6:32 am
You should have replaced
WHERE P.PRODUCT_ID IN (select PRODUCT_ID from product where name like '%' + @txt + '%')
with
WHERE FreeText(name, @txt)
Note that using FREETEXT changes the behavior of your query, since FREETEXT only matches on whole words. The original query, using LIKE, matches the specified substring, so if you were searching for 'GING', LIKE would match if GINGER was present in NAME. However, FREETEXT will not match GING with GINGER. CONTAINS() allows wildcards at the end of a search string, like this: CONTAINS(name, ' "ging*" ').
In your situation, you could use
SET @txt = ' "ging*" '
SELECT ...
WHERE CONTAINS(name, @txt) ...
Even with this, you would not get words that END in ging. That is, you can't use *ging*
Mike
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply