如何用SQL拆分逗号分隔字符串为多列，忽略双引号内的逗号

针对你给出的CSV字符串Steve,Mark,"1,2",Final，需要拆分时跳过双引号内部的逗号，以下是主流SQL数据库的实现方案：

MySQL 解决方案 #

方法1：正则表达式提取（MySQL 8.0+） #

利用MySQL 8.0及以上支持的正则函数，直接匹配符合规则的列内容：

SELECT
  REGEXP_SUBSTR(csv_str, '(?<=^|,)([^",]+|"[^"]*")(?=,|$)', 1, 1) AS col1,
  REGEXP_SUBSTR(csv_str, '(?<=^|,)([^",]+|"[^"]*")(?=,|$)', 1, 2) AS col2,
  REGEXP_SUBSTR(csv_str, '(?<=^|,)([^",]+|"[^"]*")(?=,|$)', 1, 3) AS col3,
  REGEXP_SUBSTR(csv_str, '(?<=^|,)([^",]+|"[^"]*")(?=,|$)', 1, 4) AS col4
FROM (
  SELECT 'Steve,Mark,"1,2",Final' AS csv_str
) t;

正则规则说明：匹配两种合法列内容——要么是不含逗号和引号的普通字符串，要么是被双引号包裹、内部可包含任意字符（除引号）的内容，同时确保匹配的内容是完整的列（前后为字符串开头/结尾或逗号）。

方法2：自定义函数（兼容低版本MySQL） #

如果你的MySQL版本低于8.0，没有正则函数，可以编写自定义拆分函数：

DELIMITER //
CREATE FUNCTION split_quoted_csv(str TEXT, pos INT) 
RETURNS TEXT
DETERMINISTIC
BEGIN
  DECLARE current_pos INT DEFAULT 1;
  DECLARE current_col INT DEFAULT 1;
  DECLARE in_quote BOOLEAN DEFAULT FALSE;
  DECLARE result TEXT DEFAULT '';
  
  WHILE current_pos <= LENGTH(str) AND current_col <= pos DO
    SET result = '';
    WHILE current_pos <= LENGTH(str) DO
      IF SUBSTRING(str, current_pos, 1) = '"' THEN
        SET in_quote = NOT in_quote;
        SET result = CONCAT(result, SUBSTRING(str, current_pos, 1));
        SET current_pos = current_pos + 1;
      ELSEIF SUBSTRING(str, current_pos, 1) = ',' AND NOT in_quote THEN
        SET current_pos = current_pos + 1;
        SET current_col = current_col + 1;
        LEAVE;
      ELSE
        SET result = CONCAT(result, SUBSTRING(str, current_pos, 1));
        SET current_pos = current_pos + 1;
      END IF;
    END WHILE;
    IF current_col = pos THEN
      RETURN result;
    END IF;
  END WHILE;
  RETURN NULL;
END //
DELIMITER ;

-- 调用函数拆分
SELECT
  split_quoted_csv('Steve,Mark,"1,2",Final', 1) AS col1,
  split_quoted_csv('Steve,Mark,"1,2",Final', 2) AS col2,
  split_quoted_csv('Steve,Mark,"1,2",Final', 3) AS col3,
  split_quoted_csv('Steve,Mark,"1,2",Final', 4) AS col4;

PostgreSQL 解决方案 #

PostgreSQL 14+内置了csv_parse函数，可直接处理带引号的CSV：

SELECT
  (csv_parse(csv_str)).col1,
  (csv_parse(csv_str)).col2,
  (csv_parse(csv_str)).col3,
  (csv_parse(csv_str)).col4
FROM (
  SELECT 'Steve,Mark,"1,2",Final'::text AS csv_str
) t;

如果版本低于14，可使用正则拆分函数：

SELECT
  arr[1] AS col1,
  arr[2] AS col2,
  arr[3] AS col3,
  arr[4] AS col4
FROM (
  SELECT regexp_split_to_array('Steve,Mark,"1,2",Final', '(?<=^|,)([^",]+|"[^"]*")(?=,|$)') AS arr
) t
WHERE arr[1] IS NOT NULL;

SQL Server 解决方案 #

通过将CSV转换为JSON数组，利用JSON_VALUE提取列，实现简单高效：

SELECT
  JSON_VALUE(json_str, '$[0]') AS col1,
  JSON_VALUE(json_str, '$[1]') AS col2,
  JSON_VALUE(json_str, '$[2]') AS col3,
  JSON_VALUE(json_str, '$[3]') AS col4
FROM (
  SELECT '["' + REPLACE(REPLACE('Steve,Mark,"1,2",Final', '"', '""'), ',', '","') + '"]' AS json_str
) t;

原理：先将原字符串中的双引号转义为JSON格式的双引号，再用","替换分隔逗号，最后包裹成JSON数组，即可通过JSON函数提取各列。

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