Search code examples
sql-serversearchcontainstable

FreetextTable Query

I have written an sql query to search 2 columns of a database, partNo and Description and it works fine for searching descriptions however if you search for a part number such as 164 it picks up all of the part numbers which start with 164 such as say: 164-20 but it doesn't pickup results like say APS164-20

here is my sql code, I am running mssql 2005.

SELECT FT_TBL.*, KEY_TBL.RANK
FROM Parts AS FT_TBL 
    INNER JOIN FREETEXTTABLE(Parts,(PartNo, Description),  
    '164') AS KEY_TBL
        ON FT_TBL.PartNo = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO

I tried containstable but that didn't return the rows either

Solution

  • Full text search won't cover this scenario, where you're essentially looking for the search term as a substring within the text.

    Try a traditional wildcard search instead:

    SELECT *
FROM Parts AS FT_TBL 
WHERE FT_TBL.PartNo like '%164%'