vendredi 17 juin 2016

Combine LIKE and CONTAINS in SAP HANA

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 FUZZY():

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

I only get:

  • red apple
  • red aple

When adding wildcards:

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

Why I can't combine both operators LIKE and CONTAINS in one query?

I need to find:

  • entries, where apple is surrounded by other words (in my case colors)
  • all forms of apple (regardless of the spelling)

Aucun commentaire:

Enregistrer un commentaire