How to find rows by filtering a specific text usin

问题:

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/.

Try this for XML

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

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

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.

Something like

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 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 + '%';