Splitting string based off a word

Question

I have some JSON data stored as TEXT in table column. Below is a partial snippet of what is contained in one column.

What i am trying to do is to get text that is current as col1 and text that is previous as col2. Not sure how to go about doing this. Any help will be appreciated

  "field_text": { 
    "current": "This is current text",
    "previous": "This is previous text"
  },
  "CustomerIDs": { 
    "current": "1234",
    "previous": ""
  }
}.

I am just lost on what your table looks like. What is the name of hte column? I see multiple different names in your example. What database are you using? — Gordon Linoff, Jan 07 '20 at 18:09
This is an excerpt from VARCHAR text, stored in the column. What i am trying to do is to extract the "Current" value and "Previous" value from "field_text". The name of the column is text_json. I am using a SQL Database — Arslan Ahmed, Jan 07 '20 at 19:19
This should help if it's SQl Server 2016 and above https://stackoverflow.com/questions/38285223/accessing-json-array-in-sql-server-2016-using-json-value — Radagast, Jan 07 '20 at 20:00

score 0 · Answer 1 · answered Jan 26 '20 at 00:25

Since we don't have the luxury of JSON parsers that are available with newer versions of SQL Server, here is an ugly approach that might do the trick if the structure of your JSON string is static. For simplicity, I assumed there are no spaces other than in the specific strings to be extracted. So you might want to adjust a few numbers (11,12,13) in this code to account for that. If you go through it, you'll see that it's basically a divide and conquer approach to getting the string we want.

with your_table as

(select '{"field_text":{"current":"This is current text","previous":"This is previous text"},"CustomerIDs":{"current":"1234","previous":""}}' as json_text)

select json_text, txt1, txt2
from your_table t1
cross apply (select charindex('"current":"',json_text) as i1) t2
cross apply (select charindex('"previous":"',json_text,(i1 + 1)) as i2) t3
cross apply (select substring(json_text,(i1+11),(i2-i1-13)) as txt1) t4
cross apply (select charindex('"previous":"',json_text) as i3) t5
cross apply (select charindex('"},',json_text,(i3 + 1)) as i4) t6
cross apply (select substring(json_text,(i3+12),(i4-i3-12)) as txt2) t7;

DEMO ON SQL SERVER 2012

Splitting string based off a word

1 Answers1