SQL如何拆分含双引号内逗号的字符串并提取为多列？

核心思路 #

你的输入字符串本质是被方括号包裹的标准CSV格式：字段用逗号分隔，含逗号的字段用双引号包裹，空字段用""表示。实现步骤如下：

1. 移除字符串首尾的方括号[]，得到纯CSV内容；
2. 利用数据库自带的CSV/JSON解析功能，按规则分割字段（自动识别双引号包裹的含逗号字段）；
3. 将空字符串""转换为NULL。

各数据库具体实现 #

1. PostgreSQL #

可以通过正则修正字符串为合法JSON数组，再用JSON解析函数拆分：

WITH raw_data AS (
  SELECT '[04/11/2023,"New addition","","new additional of fund","This, Adam, Kyle","5.00","11147474"]' AS input_str
),
formatted_json AS (
  SELECT
    -- 给无引号的字段补全双引号，转为合法JSON数组
    regexp_replace(input_str, '([^\["],|^\[)([^",\]]+)', '\1"\2"', 'g') AS json_str
  FROM raw_data
),
parsed_data AS (
  SELECT
    json_array_elements_text(json_str::json) AS value,
    row_number() OVER () AS col_idx
  FROM formatted_json
)
SELECT
  MAX(CASE WHEN col_idx=1 THEN value END) AS col1,
  MAX(CASE WHEN col_idx=2 THEN value END) AS col2,
  MAX(CASE WHEN col_idx=3 THEN NULLIF(value, '') END) AS col3, -- 空字符串转NULL
  MAX(CASE WHEN col_idx=4 THEN value END) AS col4,
  MAX(CASE WHEN col_idx=5 THEN value END) AS col5,
  MAX(CASE WHEN col_idx=6 THEN value END) AS col6,
  MAX(CASE WHEN col_idx=7 THEN value END) AS col7
FROM parsed_data;

2. MySQL 8.0+ #

借助JSON_TABLE函数，先修正字符串为合法JSON：

WITH raw_data AS (
  SELECT '[04/11/2023,"New addition","","new additional of fund","This, Adam, Kyle","5.00","11147474"]' AS input_str
),
formatted_json AS (
  SELECT
    REGEXP_REPLACE(input_str, '([^\\["],|^\\[)([^",\\]]+)', '\\1"\\2"', 1, 0, 'g') AS json_str
  FROM raw_data
)
SELECT
  col1,
  col2,
  NULLIF(col3, '') AS col3,
  col4,
  col5,
  col6,
  col7
FROM formatted_json,
JSON_TABLE(
  json_str,
  '$[*]' COLUMNS(
    col1 VARCHAR(50) PATH '$[0]',
    col2 VARCHAR(50) PATH '$[1]',
    col3 VARCHAR(50) PATH '$[2]',
    col4 VARCHAR(50) PATH '$[3]',
    col5 VARCHAR(50) PATH '$[4]',
    col6 VARCHAR(50) PATH '$[5]',
    col7 VARCHAR(50) PATH '$[6]'
  )
) AS jt;

3. SQL Server #

用OPENJSON解析修正后的JSON字符串：

DECLARE @input_str NVARCHAR(MAX) = '[04/11/2023,"New addition","","new additional of fund","This, Adam, Kyle","5.00","11147474"]';
DECLARE @json_str NVARCHAR(MAX) = REGEXP_REPLACE(@input_str, '([^\["],|^\[)([^",\]]+)', '\1"\2"', 1, 0);

SELECT
  col1,
  col2,
  NULLIF(col3, '') AS col3,
  col4,
  col5,
  col6,
  col7
FROM OPENJSON(@json_str)
WITH (
  col1 VARCHAR(50) '$[0]',
  col2 VARCHAR(50) '$[1]',
  col3 VARCHAR(50) '$[2]',
  col4 VARCHAR(50) '$[3]',
  col5 VARCHAR(50) '$[4]',
  col6 VARCHAR(50) '$[5]',
  col7 VARCHAR(50) '$[6]'
);

关键说明 #

• 正则表达式的作用是给未被双引号包裹的字段（比如开头的日期）补全引号，让字符串成为合法JSON数组，方便数据库原生解析；
• NULLIF(value, '')专门将空字符串转换为NULL，匹配你要的输出格式；
• 如果你的数据库不支持JSON或正则，可以自定义字符串分割函数，但逻辑会更复杂，优先推荐用原生解析功能。

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