PostgreSQL/SQL中如何将字符串内换行符替换为递增行号

需求说明 #

• 表中每行存储带换行符（char(10)）的字符串字段，需将字符串按换行拆分后，每段内容前添加从1开始的递增序号，序号与内容用.连接
• 优先不使用CTE实现

单条字段示例输入：

I
am
Awesome

对应期望输出：

1.I
2.am
3.Awesome

原有写法问题 #

之前尝试直接用REPLACE搭配表级row_number()的逻辑不成立：row_number() over(order by field)是给表中不同的数据行编号，无法识别单个字符串内部的换行位置做序号递增。

可用实现代码 #

适用于SQL Server 2022及以上版本 #

利用原生STRING_SPLIT的顺序标记能力，搭配STRING_AGG拼接，逻辑简洁性能高：

SELECT 
  STRING_AGG(CONCAT(seq, '.', value), CHAR(10)) WITHIN GROUP (ORDER BY seq) AS desired_Formula
FROM tbl
CROSS APPLY (
  SELECT 
    value,
    ROW_NUMBER() OVER (PARTITION BY tbl.id ORDER BY ordinal) AS seq
  FROM STRING_SPLIT(field, CHAR(10), 1) -- 第三个参数1表示开启输出拆分位置序号
  WHERE value <> '' -- 不需要过滤空行可删除该条件
) t
GROUP BY tbl.id, field

兼容低版本SQL Server（无CTE） #

借助系统自带的数字序列表+XML拆分逻辑，不需要自定义CTE即可保证拆分顺序和原字符串完全一致：

SELECT 
  STUFF(
    (SELECT 
      CONCAT(CHAR(10), ROW_NUMBER() OVER(ORDER BY s.number), '.', T.c.value('.', 'varchar(max)'))
    FROM master..spt_values s
    CROSS APPLY (SELECT CAST('<x>' + REPLACE(REPLACE(field, CHAR(13), ''), CHAR(10), '</x><x>') + '</x>' AS XML)) x(c)
    CROSS APPLY x.c.nodes('/x[position()=sql:column("s.number")+1]') T(c)
    WHERE s.type='P' AND s.number < x.c.value('count(/x)', 'int')
      AND T.c.value('.', 'varchar(max)') <> '' -- 不需要过滤空行可删除该条件
    FOR XML PATH(''), TYPE
  ).value('.', 'varchar(max)'), 1, 1, '') AS desired_Formula
FROM tbl

注意事项 #

• 代码中tbl.id替换为你表中实际的主键字段即可，用于区分不同原始行的拆分逻辑
• 如果字符串中包含XML特殊字符（如<、>、&），需要先做转义处理再走XML拆分逻辑，避免解析报错

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