Skip to Content
Technical Articles
Author's profile photo Pavan Patil
Pavan Patil
October 20, 2020 3 minute read

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

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%20Search%20AMDP%20Class

Fuzzy Search AMDP Class

  • Consuming AMDP Class in Report Program:

Fuzzy%20Search%20Report%20Program

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.

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Abhinav Mehrotra
      Abhinav Mehrotra

      Good One

      Author's profile photo Pavan Patil
      Pavan Patil
      Blog Post Author

      Thanks !! Abhinav Mehrotra

      Author's profile photo Prince Joshi
      Prince Joshi

      Hi Pavan Patil ,

      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

      Author's profile photo Ashlesha Vallapuri
      Ashlesha Vallapuri

      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

       

       

       

      Author's profile photo Pavan Patil
      Pavan Patil
      Blog Post Author

      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
      Author's profile photo Prabukannan S
      Prabukannan S

      Hi Pavan Patil,

      Nice Article...

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

       

      Author's profile photo Pavan Patil
      Pavan Patil
      Blog Post Author

      Hi Prabukannan,

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

      Thank You.

      Regards,

      Pavan

       

      Author's profile photo Neha Ankush
      Neha Ankush

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

      Author's profile photo Former Member
      Former Member

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