0

In my Sql Server database, I've a table containing an nvarchar(4000) field called DataStructure. In this table I've 2 records having following data into DataStructure field:

Record 1:
{"FileName":"ACO23004000","SourceUrl":"http://mysite/TEST.TIF"}

Record 2:
{"FileName":"ACO23004000","SourceUrl":"http://mysite.fm/TEST.TIF"}

I try to search the value ACO23004000 using the Contains command (full text search) in SqlServer as following:

Select *
from MyTable
where CONTAINS(DataStructure, '"*ACO23004000*"')

The query returns only the first record, seems that the ".fm" value into text prevents the search of the second record.

How can I solve this issue? Is possible that some characters prevent CONTAINS command to work correctly?

Badozvora
  • 383
  • 1
  • 3
  • 15
  • Full text search is for... texts, not jsons. Why not just do Select * from MyTable where DataStructure like '%ACO23004000%' ? – siggemannen Apr 04 '23 at 12:55
  • Note that the CONTAINS operator supports only search for prefixes not suffixes, as mentioned in the comments of [this](https://stackoverflow.com/a/33780775/12492890) answer. Check that thread, maybe could be of some help to the specific full-text search use case. – lemon Apr 04 '23 at 13:38

1 Answers1

0

You're trying to apply a condition on a json-formatted textual field. Use instead the OPENJSON operator to scrape it, and impose the corresponding condition on the filename.

SELECT MyTable.*
FROM MyTable
CROSS APPLY OPENJSON(DataStructure) WITH(FileName VARCHAR(20) '$.FileName')
WHERE FileName = 'ACO23004000'

Output:

DataStructure
{"FileName":"ACO23004000","SourceUrl":"http://mysite/TEST.TIF"}
{"FileName":"ACO23004000","SourceUrl":"http://mysite.fm/TEST.TIF"}

Check the demo here.

Note: You can define your best suitable type for "SourceUrl" field in the corresponding WITH clause of the OPENJSON operator, according to the nature of that field.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • Hi @lemon, my situation was semplified for simplicity in my question but is a little more complex. We use this query on a table containing (actually) 85.000.000 of rows and json structure can be different in all rows. So what we need is a CONTAINS function that uses a full text index to get data. In that way, our query is executed in less than 1 second. The OPENJSON command is very useful but not in our situation where we need something similar to a LIKE command (but faster, like CONTAINS with fulltext index) – Badozvora Apr 04 '23 at 13:17