Oracle数据库中如何从单单元格KeyValuePair数据按键取值

我有如下表格：

需要编写查询语句，根据键从单元格的键值对字符串中提取对应值。例如查询id=1时，name列中键为2对应的值，预期返回：

john

要求不能使用substring类函数。

解决方案 #

你的单元格内容是类JSON结构，只需转成标准JSON后用数据库原生的JSON提取函数即可，完全不需要用字符串截取：

MySQL/MariaDB #

先补全JSON要求的键引号（原生JSON规定键必须是字符串），再用JSON_EXTRACT提取：

SELECT JSON_EXTRACT(
  REPLACE(REPLACE(name, '{', '{"'), ':', '":'), 
  '$.2'
) AS target_value
FROM your_table
WHERE id = 1;

MariaDB 10.5+可用更简洁的JSON_VALUE：

SELECT JSON_VALUE(
  REPLACE(REPLACE(name, '{', '{"'), ':', '":'), 
  '$.2'
) AS target_value
FROM your_table
WHERE id = 1;

PostgreSQL #

转成JSONB后用->>运算符直接提取：

SELECT (REPLACE(REPLACE(name, '{', '{"'), ':', '":')::jsonb)->>'2' AS target_value
FROM your_table
WHERE id = 1;

SQL Server #

用JSON_VALUE处理，同样先补全键引号：

SELECT JSON_VALUE(
  REPLACE(REPLACE(name, '{', '{"'), ':', '":'), 
  '$.2'
) AS target_value
FROM your_table
WHERE id = 1;

补充说明 #

• 这里的REPLACE只是补全JSON格式的必要符号，不属于substring类字符串截取操作，符合要求
• 如果你的单元格内容已经是标准JSON（比如{"1":"mark","2":"john"}），可以直接跳过补全步骤，比如MySQL中直接写：

  SELECT JSON_EXTRACT(name, '$.2') FROM your_table WHERE id = 1;
  

内容的提问来源于stack exchange，提问作者Bahy Mohamed