Fuzzy search with wildcard character

[Former Member]

Hi,

I have requirement to have a fuzzy search over a columnar table. But the requirement demands fuzzy search with wildcard character. But when i call fuzzy search with contains using % or * as wild card character, it will do an exact match with wildcard character. Even in Sap documentation its written,

Asterisk (*) :  The asterisk activates a wildcard search. In this case, no fuzzy search is performed. An exact match with wildcards is called instead.

Percent Sign (%)  :  The percentage sign is replaced with an asterisk (*), and a wildcard search is called.

If my table TABLE1 has records,

X   Y

--------------

1   AKSHAY NAYAK

2   AKSHAY

3   AKSHAY NAYAK K

if i run below statement,

Query1:

Select * from

TABLE1

WHERE CONTAINS(Y,'%AKSHEY%',FUZZY(0.9))

Query2:

Select * from

TABLE1

WHERE CONTAINS(Y,'AKSHEY',FUZZY(0.9))

Query1 i don't get any output as an exact search will be called with wilcard character. but Query 2 i get AKSHAY as output as fuzzy will pull based on fuzzy score (>=0.9). But my requirement needs all 3 rows as output with fuzzy and wildcard search together.

Thanks in advance

Regards,

Akshay

[muthuram_shanmugavel2 · Accepted solution]

Hi Akshay,

I think you have declared Column Y as "VARCHAR" Data Type.

Please create the Table with "TEXT" Data Type.

Create column Table TEXT_1

(X Integer,

Y TEXT)

Then Insert the data and use the FUZZY SQL Query. You will get required output.

Regards,

Muthuram

[Former Member · Accepted solution]

Hi Muthuram,

i tried creating a table with TEXT datatype and i tried the above solution, it worked. But this works for situation where your search token is separated by space. if i try to search AKSH it wont match,

I should have got all 3 rows as output since AKSH should match with all of them.

Thanks,

Akshay

[former_member182302 · Accepted solution]

Hi Akshay,

The score will become less.. you can try with 0.7 as shown below:

Regards,

Krishna Tangudu

[muthuram_shanmugavel2 · Accepted solution]

Hi Akshay,

As Krishna Suggested, Lower the Fuzzy scoring level to 0.7.

You will get all 3 rows data.

Regards,

Muthuram

[Former Member · Accepted solution]

Hi Krishna,

But i dont want the score to become less as i have entered only part fo the actual token (AKSHAY). My requirement is it has to match even with 1.0 or 0.9. I dont want fuzzy to be applied for characters in my actual value other than i entered.

Thanks,

Akshay

[Former Member]

Hi Akshay,

Why dont you use low fuzzy score??

Execute the below query

Select * from TABLE1 where contains (Y,'AKSHEY',FUZZY(0.1))

Thsi will give you all the values

Regards

Priyanka

[Former Member]

Hi Priyanka,

Using low score would pull everything from table, most of which are not at all related to my search token.

Thanks,

Akshay

[Former Member]

As you have found out from the manual, you can't have a fuzzy search and a wildcard search at the same time.

As a workaround you might want to try other parts from the manual.

Option similarCalculationMode=substringsearch perhaps?

hope this helps

[Former Member]

Hi Josef,

I tried above approach but the SCORE calculation is very less that even a single change in character would result in difference of 0.4 to 0.5. so basically if there is a change in search string by 2 characters, it never shows in output.

this is the impact ,

Mode         wrong characters    additional characters in search   additional characters in table

substringsearch    high                                         high                                          low

Thanks,

Akshay