CODEWITHSUNDEEP
sqlsql-serversql-server-2017
MilkTea
MilkTea

Reputation: 73

SQL get values within quotes

im encountering the following issue i have a couple of strings in SQL and try to get just a little part off of it and i cant get it to work.

Examples of my strings: 

{"@odata.type":"#make.this.short","Id":0,"Value":"Stackoverflow"}

[{"@odata.type":"#Mmake.this.short","Id":1,"Value":"Stackoverflow1"},{"@odata.type":"#make.this.short","Id":2,"Value":"Stackoverflow2"}]

{"@odata.type":"#make.this.short","Claims":"i:0#.f|membership|[email protected]","DisplayName":"Lastname F. (Firstname)","Email":"[email protected]","Picture":"https://picture.com/","Department":null,"JobTitle":null}

Now im trying to write a stored procedure and get the following values back:

Stackoverflow

Stackoverflow1,Stackoverflow2

and [email protected]

I tried working with SUBSTRING, LEN, RIGHT, CHARINDEX and REVERSE but i cant seem to get what i need. The value of Value: can ofcourse be of different lenghts for each record.

My idea was to count the total string (len string) then get the first " on the right and subtract that from the total string. then i would try and get the second " and subtract that from the first. whats left is the value inside and i could use a substring.

But that would only work for the first one, the others are quite different and somehow i cant even get the first to do what i like. (i cant seem to get the 2nd " on the right)

i hope this is enough info so you could point me in the right direction to fix this problem.

Thankyou

Upvotes: 1

Views: 353

Answers (1)

Martin Smith
Martin Smith

Reputation: 453608

As you are on SQL Server 2017 you should be using the JSON functions for this

SELECT JSON_VALUE('{"@odata.type":"#make.this.short","Id":0,"Value":"Stackoverflow"}','$.Value')

SELECT JSON_VALUE('{"@odata.type":"#make.this.short","Claims":"i:0#.f|membership|[email protected]","DisplayName":"Lastname F. (Firstname)","Email":"[email protected]","Picture":"https://picture.com/","Department":null,"JobTitle":null}','$.Email')


SELECT STRING_AGG(JSON_VALUE(value,'$.Value') ,',')
FROM OPENJSON('[{"@odata.type":"#Mmake.this.short","Id":1,"Value":"Stackoverflow1"},{"@odata.type":"#make.this.short","Id":2,"Value":"Stackoverflow2"}]','$')

Upvotes: 2

Related Questions