What is Fuzzy Search & how to implement Fuzzy Search Technique for a Report Program using AMDP Class ?

[patil477pavan]

Introduction:

What is Fuzzy Search ? 

• A technique of finding the strings that match a pattern approximately (rather than exactly).


• A fault-tolerant search, which returns records even if the search term contains typos or additional/missing characters.


• Example: Typing ‘Wether’ in Google will also return a list of hits corresponding to ‘Weather/Whether’.


• The below figure gives us the clear idea.

Fig: Google (Source)

Fuzzy Search in SAP HANA:

• Can be applied in the WHERE clause of a SELECT statement using the function ‘CONTAINS’ with the option ‘FUZZY’


• SELECT * FROM <table_name> WHERE CONTAINS (<column_name>, <search_string>, FUZZY(x) ).


• <table_name> can be name of a column table, attribute view or a SQL view created with the CREATE VIEW statement


• FUZZY(x) specifies the degree of accuracy required between the strings used in comparison (<column_name> and <search_string>)


• ‘x’ in FUZZY(x) is called a fuzzy factor and can have values between 0 and 1.

Key Points:

• If only FUZZY is specified, it takes the value of x as 0.8


• If FUZZY(x)/FUZZY is not provided, an exact match is searched.


• CONTAINS function can also be used in a SELECT query with JOIN.


• 0 being least accuracy and 1 being highest accuracy.

What is AMDP Class ? 

• An AMDP Class is a global class existing in the class library with a tag interface IF_AMDP_MARKER_HDB (to indicate AMDP classes for the HANA Database Server)


• Can contain both regular methods and AMDP methods


• A method is indicated as an AMDP method in the implementation section of the class using the addition BY DATABASE PROCEDURE in the METHOD statement


• Can only be created using ADT.


• An AMDP method is called in an ABAP program in the same way as any other ABAP Objects method.

How to implement Fuzzy Search using AMDP Class ? 

Custom Table (ZSCARR) created as a part of this blog post and maintained the entries as shown in the below figures.

Simple Use Case:

Suppose , you have 100000's of record and you need to join multiple tables and fetch the output/data accordingly and the end user just wants to see the probable output based on the input provided by him. In this case , Fuzzy Search Technique comes handy.

• AMDP Class with Fuzzy Technique:

Fuzzy Search AMDP Class

• Consuming AMDP Class in Report Program:

Fuzzy Search Report Program

Selection Screen:

• Output when Fuzzy is  0.1

• Output when Fuzzy is  0.5

• Output when Fuzzy is  1

Summary:

• Fuzzy Search is a technique of finding strings that match a pattern approximately (rather than exactly).


• Fuzzy Search in SAP HANA makes use of two functions – CONTAINS() which performs search on strings and SCORE() which returns fuzzy score.

[amehrotra]

Good One

[patil477pavan]

Thanks !! amehrotra

[prince_joshi]

Hi patil477pavan ,

Is it possible to pass multiple entries as an input to the select query in AMDP method? Because in my scenario I need to make use of fuzzy search for multiple records and instead of calling above method in loop, I am expecting to get the result for all records at once.

 

Trying to achieve that using below but syntax error:
im_t_customer --> is an importing parameter(table type) with customer names

METHOD fuzzy_search BY DATABASE PROCEDURE

                       FOR HDB

                       LANGUAGE SQLSCRIPT

                       OPTIONS READ–ONLY

                       USING ZXYZ_TABLE.



ex_result = SELECT score( ) as score, unique_id, customer_name from ZXYZ_TABLE

WHERE CONTAINS ( customer_name, im_t_customer-ZZ_CUSTOMER_NAME, FUZZY( 0.50 )) AND MANDT = '200' ORDER BY 1;



ENDMETHOD.

ENDCLASS.

 

Could you suggest if it is possible.

 

Regards,

Prince

[prabukannans]

Hi Pavan Patil,

Nice Article...

For Dynamic Client, better you can take 'SESSION_CONTEXT( 'CLIENT' )' instead of hard coded mandt field value '100'.

 

[patil477pavan]

Hi Prabukannan,

Exactly I didn't use it in these screenshots  . Yes , its always better to use session variables.

Thank You.

Regards,

Pavan

 

[former_member678851]

Hi Prince,

Were you able to achieve it.  I am also looking for the same kind of requirement. By passing multiple input values in the search string, but contains keyword is not supporting the second argument as Column value or a table.

Can you Please help.

Thanks,

Asha

[patil477pavan]

Hi Prince ,

For multiple fuzzy search elements we can cascade in the following way:

where

(

contains( name_first, :iv_first_name, fuzzy(0.5) ) and

contains( name_last, :iv_last_name, fuzzy(0.8) )

)

Thank You.

Best Regards,

Pavan

[former_member809050]

What are the perquisites to activated Fuzzy Search in SAP HANA tables

[former_member823255]

Is it possible to give the fuzzy factor a number from a parameter ?