如何通过在 MS SQL 2012 中使用全文搜索过滤特定文本来查找行 [英] How to find rows by filtering a specific text using Full text search in MS SQL 2012

问题描述

我需要通过在 MS SQL 中使用全文搜索过滤特定文本来查找行.第一个要求是通过搜索 xml 列中的文本来查找行，第二个要求是通过搜索 json 列中的文本来查找行(nvarchar 数据类型).以下条件应返回结果.

I have a requirements to find rows by filtering a specific text using Full Text Search in MS SQL. The first requirement is to find rows by searching the text within the xml column, and the second requirement, is to find rows by searching the text within the json column(nvarchar data type). The following conditions should return a result.

XML Column
Criteria 1. Where Contains(XMLData,"1")
Criteria 2. Where Contains(XMLData,"/1/")
Criteria 3. Where Contains(XMLData,"<field>1</field>")

JSONDATA Column :
Criteria 1. Where Contains(JSONData,"1")
Criteria 2. Where Contains(JSONData,"/1/")
Criteria 2. Where Contains(JSONData,"PortalId:1")

我当前的实现是使用下面的查询，在运行数千条记录时会出现性能问题.除了下面的代码还有其他方法吗?

My current implementation is by using the query below which has a performance issues when running thousand of records. Is there any other approach other than the code below?

XML QUERY
SELECT *
WHERE cast(XMLData as nvarchar(max)) LIKE '%/' + CONVERT(VARCHAR,'1') +'/%'

JSON QUERY
SELECT *
WHERE JSONDataLIKE '%/' + CONVERT(VARCHAR,'1') +'/%'

这是这个问题的示例表.

Here is a sample table for this question.

http://sqlfiddle.com/#!18/f65ef/1

推荐答案

我认为全文搜索对您没有帮助.看来您正在寻找任何片段，甚至是像 /1/ 这样的技术术语.

I do not think that a full text search would help you. It seems you are looking for any fragment even such as technical terms like /1/.

试试这个用于 XML

 DECLARE @SearchFor VARCHAR(100)='1';

 SELECT * 
 FROM SettingsData
 WHERE xmldata.exist(N'//*[contains(text()[1],sql:variable("@SearchFor"))]')=1;

它将检查任何节点的内部text()是否包含搜索短语.但是可以找到任何包含 1 的值(例如，任何在某处具有 1 的不相关数字.)您可以搜索 text()="1" 并仅在字符串长度超过某个最小值时才执行 contains.

It will check any node's internal text() if it contains the search phrase. But any value with a 1 inside is found (e.g. any unrelated number which has a 1 somewhere.) You might search for text()="1" and perform the contains only if the string length exceeds a certain minimum.

类似的东西

WHERE xmldata.exist(N'//*[text()[1]=sql:variable("@SearchFor") or(string-length(text()[1])>=3 and contains(text()[1],concat("/",sql:variable("@SearchFor"),"/")))]')=1;

Json - 到目前为止 - 只不过是一个字符串，必须被解析.在 v2016 中，Microsoft 引入了 JSON 支持，但您使用的是 v2012.在 JSON 字符串上使用 LIKE 搜索的问题可能是，即使作为元素名称的一部分，您也会找到 1.剩下的就如上...

Json is - up to now - nothing more than a string and must be parsed. With v2016 Microsoft introduced JSON support, but you are on v2012. The problem with a LIKE search on a JSON-string might be, that you would find the 1 even as a part of an element's name. The rest is as above...

 SELECT *
 FROM SettingsData
 WHERE jsondata LIKE '%' + @SearchFor + '%';

这篇关于如何通过在 MS SQL 2012 中使用全文搜索过滤特定文本来查找行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！