FUZZY search - Giving extra WRONG results

[kgaurav2k14]

Hi Friends,

I am using fuzzy search functionality to look up for specific keywords in the description column. So, if any line item description contains these keywords, those line items will be flagged for reporting further. I am using below feature to search these keywords:

CONTAINS("ITEM_DESC", 'TA', FUZZY(0.8,''similarCalculationMode=substringsearch''))

This shows that I am looking for keyword = TA in the column ITEM_DESC and this will return all the rows having score greater than 0.8.

Coming to the issue - if my ITEM_DESC contains:

1) 'Anderson Pre-TA YM117 078 -> This line item is caught rightly as it contains TA. This is expected.

2) 'Bird Equip Rental Svcs Execute 051' -> The issue is even this is caught as this description also contains 'ta' in the description text. I want to avoid this scenario.

Even for text - 'INSTRUMENTATION SERVICES' - This is getting caught by fuzzy search as 'TA' is part of the string. What I want is only scenario 1 to happen.

Anybody, any thoughts here?

Thanks,

Gaurav

HDB 2.0 SP3

[michael_eaton3]

All those look to be correct matches. You appear to be asking for it to match TA, but not ta or TA, which is nonsense.

[kgaurav2k14]

Hi Michael,

In the first scenario if you notice, TA is an individual word and that is what I want as expected output, however in the 2nd scenario if you notice, TA string is found in the word 'Rental' and 'INSTRUMENTATION' as substring, I dont want these rows in the output.

I want to know any ideas to avoid the search results coming from 2nd scenario which is coming because of search string a part of a word. I want only fuzzy search to give results if it matches the string with an individual word.

I hope it makes sense now

Thanks,

Gaurav

[kgaurav2k14]

if you add flag with option 'i', it automatically takes care of case sensitivity. The only issue here is now it is apple to apple comparison. So, we need to include a space before and after the search string otherwise again it will catch other unwanted strings from the database. In short, now my search string is ' TA ' in the LIKE_REGEXPR. If my search string is 'TA' then it will still match my other records and I am back to square.

[yogananda]

Its better you add additional comment with UPPER in your fuzzy search

[michael_eaton3]

That's not what a fuzzy search is for, it is to find similar matches.

Your description is more like an exact whole word match, which can be achieved using LIKE_REGEXPR, see LIKE_REGEXPR documentation.