cancel
Showing results for 
Search instead for 
Did you mean: 

Combining FUZZY and LIKE

Former Member
0 Kudos

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?

Accepted Solutions (0)

Answers (3)

Answers (3)

0 Kudos

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


Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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)