Combine LIKE and CONTAINS in SAP HANA

Question

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)

Answer 1

select name from(
         SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name, 'apple', FUZZY(0.2)) --Part I
         UNION ALL
         SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name, '%apple%') --Part II
)group by name

This query basically joins the search results from contains clause with fuzzy search and normal search. You can also replace the Part II of query with like instead.