在SQL中使用正则表达式将管道、逗号分隔字符串转换为表格

实现核心逻辑 #

• 第一步：按行分隔符|将输入字符串拆分为多行，每行对应表格的一条记录
• 第二步：对每行逗号分隔的字符串，按列分隔符,拆分出3个字段，对应输出的c1、c2、c3列

MySQL 8.0+ 实现 #

WITH RECURSIVE input_str AS (
    -- 此处替换为实际输入字符串
    SELECT 'A,B,C|D,E,F|I,J,K' AS str
),
-- 生成行序号序列
row_nums AS (
    SELECT 1 AS n UNION ALL
    SELECT n + 1 FROM row_nums 
    WHERE n <= LENGTH((SELECT str FROM input_str)) - LENGTH(REPLACE((SELECT str FROM input_str), '|', '')) + 1
)
SELECT 
    REGEXP_SUBSTR(REGEXP_SUBSTR(t.str, '[^|]+', 1, rn.n), '[^,]+', 1, 1) AS c1,
    REGEXP_SUBSTR(REGEXP_SUBSTR(t.str, '[^|]+', 1, rn.n), '[^,]+', 1, 2) AS c2,
    REGEXP_SUBSTR(REGEXP_SUBSTR(t.str, '[^|]+', 1, rn.n), '[^,]+', 1, 3) AS c3
FROM input_str t
CROSS JOIN row_nums rn
WHERE REGEXP_SUBSTR(t.str, '[^|]+', 1, rn.n) IS NOT NULL;

PostgreSQL 实现 #

WITH input_str AS (
    -- 此处替换为实际输入字符串
    SELECT 'A,B,C|D,E,F|I,J,K' AS str
)
SELECT 
    (regexp_match(row_content, '([^,]+),([^,]+),([^,]+)'))[1] AS c1,
    (regexp_match(row_content, '([^,]+),([^,]+),([^,]+)'))[2] AS c2,
    (regexp_match(row_content, '([^,]+),([^,]+),([^,]+)'))[3] AS c3
FROM input_str,
-- 按|拆分多行
regexp_split_to_table(input_str.str, '\|') AS row_content;

Oracle 实现 #

WITH input_str AS (
    -- 此处替换为实际输入字符串
    SELECT 'A,B,C|D,E,F|I,J,K' str FROM DUAL
)
SELECT 
    REGEXP_SUBSTR(row_content, '[^,]+', 1, 1) c1,
    REGEXP_SUBSTR(row_content, '[^,]+', 1, 2) c2,
    REGEXP_SUBSTR(row_content, '[^,]+', 1, 3) c3
FROM input_str,
TABLE(CAST(MULTISET(
    -- 按|拆分多行
    SELECT REGEXP_SUBSTR(str, '[^|]+', 1, LEVEL) 
    FROM DUAL 
    CONNECT BY LEVEL <= REGEXP_COUNT(str, '\|') + 1
) AS SYS.ODCIVARCHAR2LIST)) t(row_content);

SQL Server 2017+ 实现 #

-- 此处替换为实际输入字符串
DECLARE @str NVARCHAR(MAX) = 'A,B,C|D,E,F|I,J,K';
WITH row_content AS (
    -- 按|拆分多行
    SELECT value AS row_str
    FROM STRING_SPLIT(@str, '|')
)
SELECT 
    REGEXP_SUBSTR(row_str, '[^,]+', 1, 1) AS c1,
    REGEXP_SUBSTR(row_str, '[^,]+', 1, 2) AS c2,
    REGEXP_SUBSTR(row_str, '[^,]+', 1, 3) AS c3
FROM row_content;

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