I would like to be able to split a string and select one of the fields. The SPLIT function returns a JSON array, so I can't use that directly. There is a SUBSTRING function, but there does not appear to be any way to find the offset of a partition (what MySQL calls LOCATE or POSITION).

One way to address this would be to give SPLIT an optional third parameter, so that

mysql> SELECT SPLIT('a,b,c', ',');
'["a","b","c"]'
mysql> SELECT SPLIT('a,b,c', ',', 2);
'b'

Another would be to add a LOCATE (or FIND or POSITION or INDEX) function to find the offset of a subfield, e.g.,

mysql> SELECT LOCATE('a,b,c', ',');
2
mysql> SELECT SUBSTRING('a,b,c', 0, LOCATE('a,b,c', ',') - 1);
'a'

The first is probably better, since LOCATE would probably want a "skip" parameter anyway to specify which of the matches to stop on. But the second would also work.