如何按预定义行长度将VARCHAR拆分为多行？

假设我们有目标字符串："The quick brown fox jumps over the lazy dog"，指定行长度为15时，拆分后结果如下：

split
---------------
The quick brown
fox jumps over
the lazy dog

针对这个需求，不同数据库可以用这些方法实现：

MySQL 8.0+ 实现 #

用递归CTE（公共表表达式）循环截取字符串：

WITH RECURSIVE split_strings AS (
    SELECT 
        1 AS row_num,
        SUBSTRING(column_name, 1, 15) AS split_part,
        SUBSTRING(column_name, 16) AS remaining_str
    FROM your_table
    WHERE column_name IS NOT NULL AND column_name != ''
    UNION ALL
    SELECT 
        row_num + 1,
        SUBSTRING(remaining_str, 1, 15),
        SUBSTRING(remaining_str, 16)
    FROM split_strings
    WHERE remaining_str IS NOT NULL AND remaining_str != ''
)
SELECT split_part AS split
FROM split_strings;

把代码里的your_table和column_name换成实际表名和字段名，15替换成需要的行长度即可。递归逻辑是先截前15个字符，剩下的部分继续重复截取，直到没有剩余内容。

PostgreSQL 实现 #

利用generate_series生成行数，配合substring批量截取：

SELECT 
    SUBSTRING(column_name FROM (n * 15 - 14) FOR 15) AS split
FROM 
    your_table,
    generate_series(1, CEIL(LENGTH(column_name) / 15.0)::INT) AS n
WHERE column_name IS NOT NULL AND column_name != '';

generate_series会根据字符串总长度算出需要拆分的行数，然后逐行截取对应位置的15个字符。

SQL Server 实现 #

用递归CTE结合LEFT和STUFF处理：

WITH split_strings AS (
    SELECT 
        1 AS row_num,
        LEFT(column_name, 15) AS split_part,
        STUFF(column_name, 1, 15, '') AS remaining_str
    FROM your_table
    WHERE column_name IS NOT NULL AND column_name != ''
    UNION ALL
    SELECT 
        row_num + 1,
        LEFT(remaining_str, 15),
        STUFF(remaining_str, 1, 15, '')
    FROM split_strings
    WHERE remaining_str IS NOT NULL AND remaining_str != ''
)
SELECT split_part AS split
FROM split_strings;

LEFT取前15个字符，STUFF清空已截取的部分，递归处理剩余字符串。

注意事项 #

• 所有示例里的15都可以替换成自定义的行长度变量
• 最后一行如果不足指定长度，会自动保留剩余内容
• 记得过滤空值，避免无效处理

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