Searching partial and or full word from full-text search those might be precise or fuzzy (less precise) OR matching the meaning should return correct results

_{Monday, May 16, 2011 7:56 AM}

Hi,

I have configured FULL-TEXT search on a table that has several columns, i.e., FirstName, LastName, ShortDescription, LongDescription, Titlle, Tag, URL. These all are configured/selected in seach catalog. There are many rows which has football, foot ball, ball, tball, etc. some has typo mistakes. I wanted to search all those that contains 'ball' or 'bal' or any partial work like 'oot' should return me all those precise or fuzzy (less precise) OR matching the meaning that has some partial text matching to my seach criterial, from begining, from end or from middle of the word, it should return me correct values but it won't. I trield both of following:

SELECT *, KEY_TBL.RANK
 FROM MyLookupTable FT_MyLookupTable INNER JOIN
CONTAINSTABLE(MyLookupTable, * , '"tball*"')
AS KEY_TBL
ON FT_MyLookupTable.Id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
;

and:

SELECT * ,RANK    
 FROM       
 MyLookupTable AS FT_MyLookupTable  WITH (NOLOCK)       
 INNER JOIN FREETEXTTABLE(MyLookupTable, *, '"footb*"' ) AS KEY_TBL      
 ON FT_MyLookupTable.ID = KEY_TBL.[KEY]
;

Please help.Shamshad Ali

_{Wednesday, May 18, 2011 9:21 AM ✅Answered}

Hi Shamshad Ali,

Full-text search performs on a linguistic search against the data, operating on words and phrases based on rules of a particular language.

As to using CONTAINSTABLE or CONTAINS, while the character “*” at the end of the contains search condition which is included with double quotation marks (“), for example, CONTAINSTABLE(MyLookupTable, * , '"tball*"'), it will return the word or phrase begin with “tball”. However, the character “*” at the beginning or in the middle of the contains search condition will be ignored because it only represents an asterisk.

As to using FREETEXTTABLE or FREETEXT, the character “*” will be ignored. For example, FREETEXTTABLE(MyLookupTable, *, '"footb*"' ), it will return the word or phrase equal to “footb”. It is the same while you use single quotation mark (') or single quotation mark (') and Double quotation marks (“).

For more information, Please refer to: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE.

I wanted to search all those that contains 'ball' or 'bal' or any partial work like 'oot' should return me all those precise or fuzzy (less precise) OR matching the meaning that has some partial text matching to my seach criterial, from begining, from end or from middle of the word, it should return me correct values but it won't.

You may have to use the LIKE clause to approach the effect for such Relatively exact matching.

Best Regards,
Stephanie Lv

_{Thursday, May 19, 2011 5:28 AM}

So that means i may use the UNION of results one from ContainsTable and one from LIKE caluse. would it be a good idea ? Please correct me if I am using wrong approach... or give me some better idea to fulfill all my requirements, is there any solution you have?

Shamshad Ali.

Shamshad Ali

_{Thursday, May 19, 2011 10:35 AM}

Hi,

As suggested above, in your scenario, a better solution might be using LIKE clause. For example, you can’t return the result which is partial text matching 'oot' from “football, foot ball, ball, tball” using FULL-TEXT search.

Best Regards,
Stephanie Lv