如何用基础SQL将超长字符串按分隔符解析为多列（避免重复代码）

我查过不少相关的解决方案，但这些方案都需要手动创建每一列——可我要拆分出最多90列，实在不想重复写90次类似,nullif(split_part(my_string,'|',N),'') string_N的代码。之前我试过手动编写如下解决方案，但处理超长字符串时存在问题，而且不能用Python：

with test_data (id, my_string) as
(
    select 1, 'a|b|c|d' union all
    select 2, 'abba|zabba|beta' union all
    select 5, 'x|y' union all
    select 4, 'z1|z2|z3'
)
select 
    id 
    ,nullif(split_part(my_string,'|',1),'') string_1 
    ,nullif(split_part(my_string,'|',2),'') string_2 
    ,nullif(split_part(my_string,'|',3),'') string_3 
    ,nullif(split_part(my_string,'|',4),'') string_4 
from test_data ;

方案1：动态SQL自动生成查询（以PostgreSQL为例） #

核心思路是让数据库帮你自动拼接出90列的表达式，不用手动重复编写。

生成可执行的SQL语句

先运行这段代码，它会输出包含所有90列的完整查询语句：

WITH column_defs AS (
  SELECT ', nullif(split_part(my_string, ''|'', ' || n || '), '''') AS string_' || n AS col_expr
  FROM generate_series(1, 90) AS n
)
SELECT 'WITH test_data (id, my_string) AS (
    SELECT 1, ''a|b|c|d'' UNION ALL
    SELECT 2, ''abba|zabba|beta'' UNION ALL
    SELECT 5, ''x|y'' UNION ALL
    SELECT 4, ''z1|z2|z3''
)
SELECT id ' || string_agg(col_expr, '') || ' FROM test_data;' AS dynamic_sql
FROM column_defs;

把输出的结果复制出来直接执行，就能得到拆分后的多列数据。

直接执行动态SQL

如果想一步到位自动执行，用DO块配合EXECUTE：

DO $$
DECLARE
  dynamic_sql TEXT;
BEGIN
  WITH column_defs AS (
    SELECT ', nullif(split_part(my_string, ''|'', ' || n || '), '''') AS string_' || n AS col_expr
    FROM generate_series(1, 90) AS n
  )
  SELECT 'SELECT id ' || string_agg(col_expr, '') || ' FROM test_data' INTO dynamic_sql
  FROM column_defs;

  EXECUTE format(dynamic_sql);
END $$;

注意：执行前要确保test_data是你实际使用的表或CTE。

方案2：数组函数+动态SQL简化代码（PostgreSQL） #

先把字符串转成数组，再通过数组索引提取列，结合动态SQL也能避免重复代码：

WITH test_data (id, my_string) AS (
    SELECT 1, 'a|b|c|d' UNION ALL
    SELECT 2, 'abba|zabba|beta' UNION ALL
    SELECT 5, 'x|y' UNION ALL
    SELECT 4, 'z1|z2|z3'
),
split_arr AS (
  SELECT id, regexp_split_to_array(my_string, '\|') AS str_arr
  FROM test_data
)
SELECT 
  id,
  nullif(str_arr[1], '') AS string_1,
  nullif(str_arr[2], '') AS string_2,
  -- 这里的列表达式同样可以用方案1的动态SQL生成
  nullif(str_arr[90], '') AS string_90
FROM split_arr;

其他数据库的通用思路 #

如果用MySQL、SQL Server等数据库，核心逻辑还是动态生成列表达式：

• MySQL：用SUBSTRING_INDEX配合数字序列生成列，通过GROUP_CONCAT拼接SQL语句
• SQL Server：用STRING_SPLIT结合PIVOT，或者生成数字序列拼接SUBSTRING相关的列表达式

举个MySQL的示例：

SET @cols = NULL;
SELECT GROUP_CONCAT(
  'NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(my_string, ''|'', ', n, '), ''|'', -1), '''') AS string_', n
) INTO @cols
FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL ... UNION ALL SELECT 90) AS nums;

SET @sql = CONCAT(
  'SELECT id, ', @cols, ' FROM test_data;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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