SQL如何将特殊字符分隔的单字段内容拆分为多个独立列

核心思路：你的存储内容是标准CSV行格式，每个字段值被双引号包裹，分隔符为","，拆分时按分隔符定位对应字段的区间，再移除首尾残留的双引号即可，你之前硬编码位置的写法不通用，字段内容长度变化后就会取值错误。

以下是不同主流数据库的实现方案：

MySQL 实现 #

SELECT
  -- 取第一个字段
  TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(data, '","', 1), '","', -1)) AS col1,
  -- 取第二个字段
  TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(data, '","', 2), '","', -1)) AS col2,
  -- 取第三个字段
  TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(data, '","', 3), '","', -1)) AS col3
FROM 你的表名;

逻辑说明：SUBSTRING_INDEX(data, '","', N) 会截取出前N个分隔符之前的所有内容，再取最后一段就是第N个字段的原始内容，用TRIM去掉两边的双引号即可拿到纯值。

Oracle 实现 #

用正则匹配直接提取对应位置的字段：

SELECT
  TRIM('"' FROM REGEXP_SUBSTR(data, '"([^"]*)"', 1, 1, NULL, 1)) AS col1,
  TRIM('"' FROM REGEXP_SUBSTR(data, '"([^"]*)"', 1, 2, NULL, 1)) AS col2,
  TRIM('"' FROM REGEXP_SUBSTR(data, '"([^"]*)"', 1, 3, NULL, 1)) AS col3
FROM 你的表名;

PostgreSQL 实现 #

先转数组再按序号取值：

SELECT
  trim('"' FROM (string_to_array(data, '","'))[1]) AS col1,
  trim('"' FROM (string_to_array(data, '","'))[2]) AS col2,
  trim('"' FROM (string_to_array(data, '","'))[3]) AS col3
FROM 你的表名;

SQL Server 实现（2016及以上版本支持） #

WITH split_data AS (
  SELECT 
    data,
    value,
    ROW_NUMBER() OVER (PARTITION BY data ORDER BY (SELECT NULL)) AS rn
  FROM 你的表名
  CROSS APPLY STRING_SPLIT(data, '","')
)
SELECT
  TRIM('"' FROM MAX(CASE WHEN rn = 1 THEN value END)) AS col1,
  TRIM('"' FROM MAX(CASE WHEN rn = 2 THEN value END)) AS col2,
  TRIM('"' FROM MAX(CASE WHEN rn = 3 THEN value END)) AS col3
FROM split_data
GROUP BY data;

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