on 10-28-2015 3:18 PM
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?
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:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Consider the following entries in my table:
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:
With:
SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE contains(name, '%apple%', FUZZY(0.5))
I only get all entries, where 'apple' was spelled right:
That's why I want to combine both operators LIKE and CONTAINS in order to find:
User | Count |
---|---|
71 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.