SQL中如何使用regexp_extract将逗号分隔字段值拆分为多行

问题说明 #

现有表the_table，其中customer_value字段存储逗号分隔的多值数据，初始表数据如下：

SELECT * FROM the_table;

使用regexp_extract函数仅能提取指定位置的分隔值生成新列，无法实现拆分为多行的效果，示例写法的输出如下：

SELECT *,
regexp_extract(customer_value,"^(?:[^,]*,){0}([^,]*)(?:[^,]*,){1}([^,]*)",1) as value_1,
regexp_extract(customer_value,"^(?:[^,]*,){0}([^,]*)(?:[^,]*,){1}([^,]*)",2) as value_2
FROM the_table;

预期输出为每个分隔值单独占一行，保留原表其他字段：

实现方案 #

regexp_extract是值提取函数，不具备行展开能力，这类场景需要使用字符串拆分+表生成函数实现，不同SQL引擎的写法略有区别：

Hive/Spark SQL #

用split将字符串按逗号拆分为数组，再用explode将数组展开为多行，搭配LATERAL VIEW实现原表字段关联，是大数据场景最常用的写法：

SELECT 
  customer_id,
  customer_value,
  single_val AS customer_value_comma
FROM the_table
LATERAL VIEW explode(split(customer_value, ',')) t AS single_val;

PostgreSQL #

直接用string_to_array转数组后unnest展开即可，不需要额外侧视图语法：

SELECT
  customer_id,
  customer_value,
  unnest(string_to_array(customer_value, ',')) AS customer_value_comma
FROM the_table;

MySQL 8.0+ #

MySQL没有内置数组展开函数，可通过递归CTE循环拆分字符串实现：

WITH RECURSIVE split_temp AS (
  SELECT
    customer_id,
    customer_value,
    SUBSTRING_INDEX(customer_value, ',', 1) AS customer_value_comma,
    SUBSTRING(customer_value, LENGTH(SUBSTRING_INDEX(customer_value, ',', 1)) + 2) AS remain
  FROM the_table
  UNION ALL
  SELECT
    customer_id,
    customer_value,
    SUBSTRING_INDEX(remain, ',', 1) AS customer_value_comma,
    SUBSTRING(remain, LENGTH(SUBSTRING_INDEX(remain, ',', 1)) + 2) AS remain
  FROM split_temp
  WHERE remain IS NOT NULL AND remain != ''
)
SELECT customer_id, customer_value, customer_value_comma FROM split_temp;

优化提示 #

• 如果字段存在首尾多余逗号、空值的情况，拆分前可以先用regexp_replace(customer_value, '^,|,$', '')清洗字符串，避免生成无意义的空行。
• 分隔符不是逗号时，直接替换拆分函数里的逗号参数为实际分隔符即可。

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