Skip to Content
avatar image
Former Member

Combining FUZZY and LIKE

Suppose I have a column with names, one of them is Peter.

When doing:

SELECT * FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name,'%pete%')

or

SELECT * FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name,'%pete%', FUZZY(0.7))

or

SELECT * FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name,'peteX', FUZZY(0.7))

I get the desired result Peter.

But

SELECT * FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name,'%peteX%', FUZZY(0.7))

doesn't work. I just get an empty string.

So combining the wildcards % and the fuzzy search doesn't seem to work in my example.

I'm executing this query from my XS application with AJAX so that it is executed "as-you-type".

So if you enter "p" or "pe" or "pet" it should return"Peter". But also if you misspell it as "petr" for example.

Does anybody have a suggestion?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Oct 29, 2015 at 02:22 AM

    Hi,

    Why do you want to combine fuzzy search and like? I think they should be used in different scenarios, like for exact match and fuzzy search for search like google.

    Best regards,

    Wenjun

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Consider the following entries in my table:

      • red apple
      • yellow apple
      • apple green
      • red aple
      • appel yellow

      Several people have populated this table using a non-consistend notation (the color before or after 'apple'), also entering some spelling errors.

      Now I want to query all entries with the word 'apple', regardless of color or spelling.

      With:

      SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name, 'apple', FUZZY(0.5))
      

      I only get:

      • red apple
      • red aple

      With:

      SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name, '%apple%', FUZZY(0.5))
      

      I only get all entries, where 'apple' was spelled right:

      • red apple
      • yellow apple
      • apple green

      That's why I want to combine both operators LIKE and CONTAINS in order to find:

      • entries, where 'apple' is surrounded by other words (in my case colors)
      • all forms of 'apple' (regardless of the spelling)
  • avatar image
    Former Member
    Oct 29, 2015 at 09:41 AM

    Hi Dol,

    I guess you have to divide the query into two steps or write a nested query like below which should solve your issue.

    SELECT name FROM

    (

    SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1"

    WHERE name like :<input_variable> || '%'

    )

    WHERE contains(name, :<input_variable>, FUZZY(0.7))

    ;

    This query would give the results you want to achieve. But the problem with this query is the CONTAINS predicate wont work on nested query, you will get a feature not supported error. To tackle that :


    I think first we need to create an attribute view with input parameter which does the inner nested query operation.


    SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1"

    WHERE name like :<input_variable> || '%';


    Then we can use the contains predicate on this view to attain the required result.


    SELECT name FROM <above_created_attribute_view> WHERE contains(name, :<input_variable>, FUZZY(0.7));


    Regards,

    Anil

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 19, 2015 at 07:51 AM

    Hello Dol,

    Another simpler approach is to create a "Full Text Index" on the Column which you want to enable search capabilities on it.

    Context

    A full-text index is an additional data structure that is created to enable text search features on a specific column in a table. Conceptually, full-text indexes support searching on columns in the same way that indexes support searching through books.

    Usage

    When you create a TEXT or SHORTTEXT column in a table, SAP HANA automatically creates a corresponding full-text index. For columns of other data types, however, you have to manually create and define any required full-text indexes.

    References: Taken from SAP_HANA_Developer_Guide_en_SPS08.pdf on Page 598 / 800
    * latest developer guide link (alternative guide you can search up Full Text Index)

    For your scenario, I'd suggest something like this:

    1. Execute the Following
      CREATE FULLTEXT INDEX INDEX_VARIABLE_NAME
      ON "NEO_123456789ABCDE"."MYTABLE1"("MYCOLUMN1")
      FUZZY SEARCH INDEX ON;

      What it does?
      An index "INDEX_VARIABLE_NAME " is created in your database table (check Index Package) to assume that data structure.

    2. Execute your Query

      Instead of :
      SELECT * FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name,'%peteX%', FUZZY(0.7))

      Execute this :
      SELECT * FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name,'peteX', FUZZY(0.7))

    3. Play around with Fuzziness Score

    I hope this helps.

    Please let me know if it works. (Not sure if free trial version will enable that feature though)

    Thanks.

    Regards,

    Jacob Tan


    Add comment
    10|10000 characters needed characters exceeded