如何将SQL表中字符串列拆分为单字符对应的多行数据

核心思路是绕过STRING_SPLIT无分隔符拆分的限制，通过生成和字符串长度匹配的连续数字序列，逐个截取对应位置的字符实现拆分。

适配SQL Server实现（你提到的STRING_SPLIT为SQL Server内置函数） #

WITH RECURSIVE num_seq AS (
    -- 生成连续数字序列，起始值为1
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_seq 
    WHERE n <= (SELECT MAX(LEN(value)) FROM your_table)
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY t.id, ns.n) AS id,
    SUBSTRING(t.value, ns.n, 1) AS value
FROM your_table t
INNER JOIN num_seq ns 
    ON ns.n <= LEN(t.value)
ORDER BY id
-- 若字符串长度超过100，需加下面的配置解除递归深度限制
-- OPTION (MAXRECURSION 0)

将代码中的your_table替换为你的实际表名即可运行。如果处理的字符串长度固定较短，也可以用更简单的硬编码写法：

SELECT 1 AS id, SUBSTRING(value,1,1) AS value FROM your_table
UNION ALL
SELECT 2 AS id, SUBSTRING(value,2,1) AS value FROM your_table
UNION ALL
SELECT 3 AS id, SUBSTRING(value,3,1) AS value FROM your_table
UNION ALL
SELECT 4 AS id, SUBSTRING(value,4,1) AS value FROM your_table

其他常用数据库实现参考 #

MySQL #

WITH RECURSIVE num_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_seq 
    WHERE n <= (SELECT MAX(CHAR_LENGTH(value)) FROM your_table)
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY t.id, ns.n) AS id,
    SUBSTRING(t.value, ns.n, 1) AS value
FROM your_table t
INNER JOIN num_seq ns ON ns.n <= CHAR_LENGTH(t.value)
ORDER BY id;

PostgreSQL #

SELECT 
    ROW_NUMBER() OVER(ORDER BY t.id, gs.n) AS id,
    SUBSTRING(t.value, gs.n, 1) AS value
FROM your_table t
INNER JOIN generate_series(1, (SELECT MAX(LENGTH(value)) FROM your_table)) gs(n) 
    ON gs.n <= LENGTH(t.value)
ORDER BY id;

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