是否有"LIKE"的组合?和"IN"表示在SQL中? [英] Is there a combination of "LIKE" and "IN" in SQL?
问题描述
在SQL I中，(不幸的)我经常不得不使用"LIKE"条件，因为数据库违反了几乎所有规范化规则.我现在无法更改.但这与问题无关.
In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.
此外，我经常使用WHERE something in (1,1,2,3,5,8,13,21)之类的条件来提高SQL语句的可读性和灵活性.
Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.
有什么可能的方式可以在不编写复杂的子选择的情况下将这两件事结合起来?
Is there any possible way to combine these two things without writing complicated sub-selects?
我想要像WHERE something LIKE ('bla%', '%foo%', 'batz%')一样简单的东西，而不是这个:
I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of this:
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
我在这里使用SQl Server和Oracle，但是我很感兴趣是否可以在任何RDBMS中实现.
I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.
推荐答案
没有LIKE&在SQL中为IN，在TSQL(SQL Server)或PLSQL(Oracle)中则少得多.部分原因是因为建议使用全文搜索(FTS).
There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.
Oracle和SQL Server FTS实施都支持CONTAINS关键字，但是语法仍然略有不同:
Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
SQL Server:
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
您要查询的列必须是全文索引.
The column you are querying must be full-text indexed.
参考:
- Building Full-Text Search Applications with Oracle Text
- Understanding SQL Server Full-Text
这篇关于是否有"LIKE"的组合?和"IN"表示在SQL中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
