Full-Text Indexing Issue -- Please Reply

  • 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

  • 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