0

I want that in this text:

"itemDescription":"Pack of 20 Chocolate Biscuits","inventoryUnitMeasureCode":"pack","Pack of 20 conversionFactor":"1","isMedicalItem":"No","isDangerousGoods":"No"

to search for a substring like "20 Chocolate" but only if is after "itemDescription": and between 2 of " after that itemDescription.

This is a serialized data.

I want to search if for property itemDescription I have substring 20 Chocolate, and I don't want to deserialize this string because sql is much faster to execute a where with regex

I use SQL server v17.9 but this code i will put in C#

MORE EXPECTED RESULTS

1.

"itemDescription":"Pack of 20 Chocolate Biscuits","inventoryUnitMeasureCode":"pack","Pack of 20 conversionFactor":"1","isMedicalItem":"No","isDangerousGoods":"No"

search for Pack in itemDescription result true

2.

"itemDescription":"Pack of 20 Chocolate Biscuits","inventoryUnitMeasureCode":"pack","Pack of 20 conversionFactor":"1","isMedicalItem":"No","isDangerousGoods":"No"

search for No or isMedicalItem or " result false

with other words I want to search only in this text

"itemDescription":"Pack of 20 Chocolate Biscuits" even is more text near it

SOLUTION

regex = "itemDescription"\s*:\s*"[^"]*(searchText)[^"]*"

Alex
  • 1,013
  • 1
  • 13
  • 27
  • Show us some more sample table data and also the expected result. And add the tag for the dbms used. – jarlh Aug 21 '19 at 12:45
  • Done, I added... – Alex Aug 21 '19 at 12:51
  • Is that text part of a JSON string? – MonkeyZeus Aug 21 '19 at 12:58
  • yes, it is a c# class serialized with json – Alex Aug 21 '19 at 12:59
  • 2
    Why are you avoiding native JSON functions in SQL server? https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017 – MonkeyZeus Aug 21 '19 at 13:03
  • You can use something like `"itemDescription":"[^"]*20 Chocolate[^"]*"` but it is far from perfect and good luck handling escaped double quotes which are part of the JSON's value. – MonkeyZeus Aug 21 '19 at 13:04
  • 1
    The data you are showing is JSON (or rather, in its full context it obviously is...you have left out important parts of the JSON). So, you should parse the data and _then_ do your investigation of the contents. See marked duplicates. – Peter Duniho Aug 21 '19 at 16:55

0 Answers0