selecting the text between the final two speech marks - MS SQL

B@Th*nG · 16 Jan 2020 at 09:01

hi guys,

does anyone know how to extract the text between the final two speech marks?

[{"Id":0,"ProjectId":[],"Field":"SLA","Operator":"Equal","Value":"Dev P1"},{"Id":0,"ProjectId":[],"Field":"Resource","Operator":"Equal","Value":"6,8,11,12,17,19,28,29,31,32,34,35,38,47,54,56,57,59,60,62,64,65,67,68,71,72,74,76,78,81,82,83,85,87,88,89,99,101,113,117,128,130,133,136,146,150,153,160,163,164,167,174,175,176,181,184,186,192,198,199,200,203,204,207,213,214,215,216,219,220,221,225,227,229,230,231,236,244,245,247,251,252,256,257,258,260,263,270,281,282,283,284,285,289,290,292,293,300,306,309,311,312,313,319,321,323,329,331,332,333,334,337,340,342,343,345,346,348,351"}]

the length of the fields can change and i keep ballsing it up haha. any ideas?

Many thanks,

B@

chroniclard · 16 Jan 2020 at 09:42

This bit?

"6,8,11,12,17,19,28,29,31,32,34,35,38,47,54,56,57,59,60,62,64,65,67,68,71,72,74,76,78,81,82,83,85,87,88,89,99,101,113,117,128,130,133,136,146,150,153,160,163,164,167,174,175,176,181,184,186,192,198,199,200,203,204,207,213,214,215,216,219,220,221,225,227,229,230,231,236,244,245,247,251,252,256,257,258,260,263,270,281,282,283,284,285,289,290,292,293,300,306,309,311,312,313,319,321,323,329,331,332,333,334,337,340,342,343,345,346,348,351"

Where is the text from? What are you using? Is this all from a single field in SQL?

GravyMonster · 16 Jan 2020 at 10:05

MSSQL has built in methods to deserialise JSON within queries: https://docs.microsoft.com/en-us/sq...on/json-data-sql-server?view=sql-server-ver15

I can't imagine it's very performant, though. Is it possible to re-structure the table to avoid storing JSON? Or at least, to not store values needed for querying within JSON.

B@Th*nG · 16 Jan 2020 at 10:33

chroniclard said:
This bit?

"6,8,11,12,17,19,28,29,31,32,34,35,38,47,54,56,57,59,60,62,64,65,67,68,71,72,74,76,78,81,82,83,85,87,88,89,99,101,113,117,128,130,133,136,146,150,153,160,163,164,167,174,175,176,181,184,186,192,198,199,200,203,204,207,213,214,215,216,219,220,221,225,227,229,230,231,236,244,245,247,251,252,256,257,258,260,263,270,281,282,283,284,285,289,290,292,293,300,306,309,311,312,313,319,321,323,329,331,332,333,334,337,340,342,343,345,346,348,351"

Where is the text from? What are you using? Is this all from a single field in SQL?

That bit, yes.

This is all in a single field in SQL. It's a database of a third party program so i can't change how the data is stored. It's a list of userIDs.

B@

chroniclard · 16 Jan 2020 at 10:46

SQL 2016 and later have JSON and string split functionality. Could grab the data with a json query then string split.

Earlier versions of SQL you can easily code(steal from stackoverflow) a string split function

Otherwise could do it in C#.

B@Th*nG · 16 Jan 2020 at 12:21

i know how to split between 2 characters but i don't know how to split between the last 2 instances of that character - this is what i need help with. There are multiple speech marks and the length of the string changes each time. It is always at the end of the string though.

B@

Pho · 16 Jan 2020 at 15:54

Spunkey said:
MSSQL has built in methods to deserialise JSON within queries: https://docs.microsoft.com/en-us/sq...on/json-data-sql-server?view=sql-server-ver15

I can't imagine it's very performant, though. Is it possible to re-structure the table to avoid storing JSON? Or at least, to not store values needed for querying within JSON.

You can actually do some nice things like create computed columns based on a source JSON column. SQL server automatically extracts the value based on your JSON selector and you can then index it for speed.

A use case for us is we dump the entire JSON result from a DarkSky weather API query (example: https://gist.github.com/morozgrafix/d6bace8dc3ae7e3067de7ce8a6c1b8bd) into a column and have a computed column to pull out the fields we need (temperature/summary basically) which are indexed.

https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-ver15

B@Th*nG said:
hi guys,

does anyone know how to extract the text between the final two speech marks?

B@

Use the built-in JSON queries as suggested, they're really good.

Code:

SELECT '[{"Id":0,"ProjectId":[],"Field":"SLA","Operator":"Equal","Value":"Dev P1"},{"Id":0,"ProjectId":[],"Field":"Resource","Operator":"Equal","Value":"6,8,11,12,17,19,28,29,31,32,34,35,38,47,54,56,57,59,60,62,64,65,67,68,71,72,74,76,78,81,82,83,85,87,88,89,99,101,113,117,128,130,133,136,146,150,153,160,163,164,167,174,175,176,181,184,186,192,198,199,200,203,204,207,213,214,215,216,219,220,221,225,227,229,230,231,236,244,245,247,251,252,256,257,258,260,263,270,281,282,283,284,285,289,290,292,293,300,306,309,311,312,313,319,321,323,329,331,332,333,334,337,340,342,343,345,346,348,351"}]' JsonData
INTO #test

-- If you want to select Value from a particular array index
SELECT JSON_VALUE(JsonData, '$[1].Value') Value FROM #Test

-- If you want to find the index dynamically, e.g., where field = resource
-- (adapted from https://stackoverflow.com/a/55608970/171703)
SELECT Value
FROM #Test t
CROSS APPLY (
   SELECT *
   FROM OPENJSON(t.JsonData, '$')
   WITH (
      Field nvarchar(100) '$.Field',
     Value nvarchar(100) '$.Value'
   )
) j
WHERE (j.Field = 'Resource')

GO
DROP TABLE #test