将布尔文本搜索转换为 SQL 查询 WHERE 子句 [英] Convert Boolean text search into SQL Query WHERE Clause

问题描述

我们希望实现一个搜索查询，该查询将对 SQL 中的特定列进行全文搜索.我们愿意直接在 SQL 中或在 C# 中进行，如下面的 TomC 建议.查询将由用户输入 -

We're looking to implement a Search query that will do a full-text search on a specific column in SQL. We're open to do it in SQL directly or in C# as suggested by TomC below. The query will be entered by users like -

(A AND B) OR (C AND D)

我们希望将此搜索文本转换为 SQL 查询，如下所示 -

We want to convert this search text into SQL query as below -

WHERE (col LIKE '%A%' AND col LIKE '%B%') OR (col LIKE '%C%' AND col LIKE '%D%')

请告知处理此问题的最佳方法.

Please advise what would be the best approach to handle this.

推荐答案

在数据库方面，我会使用 CONTAINS 如果您正在寻找模糊或不太精确的匹配.我会提到这一点，以防您将原始帖子简单化.与带有前导通配符的 LIKE 不同，它可以使用 INDEX 使其更快.以这种方式使用 LIKE 将导致表扫描.您必须创建 FULLTEXT INDEX 使用CONTAINS.

On the database side, I would use CONTAINS if you are looking for fuzzy or less precise matches. I mention this in case you made the original post simplistic. Unlike LIKE with a leading wild card, it can use an INDEX which would make it faster. Using LIKE in this fashion would result in a table scan. You will have to create the FULLTEXT INDEX to use CONTAINS.

另一种方法是使用 CHARINDEX.这可以使用索引，但不能保证.与您环境中的 LIKE 相比，我会对此进行测试.

Another approach would be to use CHARINDEX. This can use an index, but isn't guaranteed to. I'd test this compared to LIKE in your environment.

where 
      (charindex('A',col) > 0 and charindex('B',col) > 0) 
   or (charindex('C',col) > 0 and charindex('D',col) > 0)

这篇关于将布尔文本搜索转换为 SQL 查询 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！