Craig Francis

SQL

Notes on common, but easily forgotten, SQL queries:

Date Time




Sub String

https://example.com/?id=1
SELECT SUBSTRING(url, LOCATE('?', url) + 1) FROM example
id=1

JSON

{
"name": "Example",
"items": [
{
"name": "A",
"info": "A Info"
},
{
"name": "B",
"info": "B Info"
},
{
"name": "C",
"info": "C Info"
}
]
}
SELECT
JSON_VALUE(json, '$.name') AS name_value,
JSON_EXTRACT(json, '$.name') AS name_extract,
JSON_UNQUOTE(JSON_EXTRACT(json, '$.name')) AS name_quote,
JSON_EXTRACT(json, '$.items[*].name') AS item_names
FROM
example
Example
"Example"
Example
["A", "B", "C"]