SQL 在 SAP HANA 中组合 LIKE 和 CONTAINS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33433556/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine LIKE and CONTAINS in SAP HANA
提问by user1170330
Consider the following entries in my table:
考虑我的表中的以下条目：
- red apple
- yellow apple
- applegreen
- red aple
- appelyellow
- 红苹果
- 黄苹果
- AP PLE绿色
- 红色请求
- AP像素黄色
Several people have populated this table using a non-consistend notation (the color beforeor after'apple'), also entering some spelling errors.
Now I want to query all entries with the word apple, regardless of color or spelling.
有几个人使用不一致的符号（'apple'之前或之后的颜色）填充此表，还输入了一些拼写错误。现在我想用单词 查询所有条目apple，无论颜色或拼写如何。
With FUZZY():
与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 applewas spelled right:
我只得到所有条目，apple拼写正确的地方：
- red apple
- yellow apple
- applegreen
- 红苹果
- 黄苹果
- AP PLE绿色
Why I can't combine both operators LIKEand CONTAINSin one query?
为什么我不能在一个查询中同时使用LIKE和CONTAINS这两个运算符？
I need to find:
我需要找到：
- entries, where
appleis surrounded by other words (in my case colors) - all forms of
apple(regardless of the spelling)
- 条目，其中
apple被其他单词包围（在我的情况下为颜色） - 所有形式的
apple（无论拼写如何）
回答by Abhishek Jain
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.
该查询基本上将来自 contains 子句的搜索结果与模糊搜索和正常搜索结合起来。您也可以用 like 替换查询的第二部分。
回答by Pascalius
The fuzzy algorithm matches against the complete content of the column in your example. So it compares "apple" with "red apple" and "appel yellow"
模糊算法与示例中列的完整内容相匹配。所以它将“苹果”与“红苹果”和“苹果黄”进行比较
What you want is a matching against the tokens in your columns. You can achieve this by creating a fulltext index, which will tokenize the content in the columns into a fulltext index. contains() will automatically use the fulltext index.
您想要的是与列中的标记匹配。您可以通过创建全文索引来实现这一点，该索引会将列中的内容标记为全文索引。contains() 将自动使用全文索引。
drop table MYTABLE1;
create column table MYTABLE1
(
name nvarchar(100)
);
insert into MYTABLE1 (name) values ('red apple');
insert into MYTABLE1 (name) values ('yellow apple');
insert into MYTABLE1 (name) values ('apple green');
insert into MYTABLE1 (name) values ('red aple');
insert into MYTABLE1 (name) values ('appel yellow');
CREATE FULLTEXT INDEX i_MYTABLE1 ON MYTABLE1(name) FUZZY SEARCH INDEX ON SYNC;
SELECT name FROM "MYTABLE1" WHERE contains(name, 'apple', FUZZY(0.5))
回答by RMS
Perhaps you should try the following standard sql query :
也许您应该尝试以下标准 sql 查询：
SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE name like '%apple%'
This query will select your apples. Concerning your fuzzy, why not using it with a subquery ? Something like :
此查询将选择您的苹果。关于您的模糊，为什么不将它与子查询一起使用？就像是 ：
SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1"
WHERE contains(name, 'apple', FUZZY(0.5))
and name in (
SELECT name FROM "NEO_123456789ABCDE"."MYTABLE1" WHERE name like '%apple%'
)
