SQLTeam.com | Weblogs | Forums

Parsing a string using Standard SQL


#1

Using Standard SQL (not TSQL or other flavors), how can the string below be parsed so as to pull out each event element so as to place the info into a table?

[{"Event":"start","EventDateTime":"2015-09-15T03:45:16.681428Z"}, {"Event":"process","EventDateTime":"2015-09-15T03:45:16.681428Z"},{"Event":"end","EventDateTime":"2015-09-15T03:45:16.681428Z"}]

The final result ought be like this:
Event | EventDateTime
start | 2015-09-15T03:45:16.681428Z
process | 2015-09-15T03:45:16.681428Z
end | 2015-09-15T03:45:16.681428Z

Thanks!


#2

kinda hard to answer completely since, AFAIK no one implements the ANSI SQL specification completely. That means that any solution may not be properly tested. Note, however that several vendors (including Microsoft with SQL Server 2016) support parsing JSON out of the box.


#3

I don't think that's possible. For example, even substring is different in virtually every sql flavor.