我在mySQL Regex_replace模式上需要一些帮助。我想在JSON字符串上获取第一个值:
[{"lbl":"mobile","val":"987 654 321"},
{"lbl":"home","val":"123 456 789"}]
SELECT REGEX_REPLACE(`phones`, PATTERN, '') FROM table;
所以,我想获得的是 987 654 321 ,该工作将有什么模式?
使用 substring 和找到扩展解决方案P> 当SELECT
SUBSTRING(chain,
LOCATE('"val":"', phones) + 7, -- considering offset as starting position for the final substring
LOCATE('"}', phones, LOCATE('"val":"', phones)) - (LOCATE('"val":"', phones) + 7)) AS phone
FROM
table
'"val":"'
占据7个字符时,电话号码值应从LOCATE('"val":"', phones) + 7