有没有“LIKE"的组合?和“IN"在 SQL 中?
在 SQL 中，我(遗憾地)经常不得不使用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 服务器:
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
您查询的列必须是全文索引.
The column you are querying must be full-text indexed.
参考:
- 使用 Oracle Text 构建全文搜索应用程序
- 了解 SQL Server Full-文本
相关文章