如何按字符出现顺序将字符串按ID分组拆分为多行？

现有一张包含id列和字符串列的表，测试数据构造如下：

with fake_data(id, ex_character) as (
    select * from values
        ('A', 'T70891'),
        ('B', 'RT9811111')
)

需要按每个id将字符串中的字符按出现顺序拆分成行，预期输出如下：

id   value
A     T
A     7
A     0
A     8
A     9
A     1
B     R
B     T
B     9
B     8
B     1
B     1
B     1
B     1
B     1

目前尝试的语句如下：

select fd.id, f.value 

from fake_data fd, 
lateral split_to_table(regexp_replace(trim(fd.ex_character), '.', './/0', 2), ',') f

但返回结果无序，请问如何获取符合预期的有序拆分结果？

你当前的方法依赖正则替换拆分字符串，但split_to_table本身不保证拆分结果的顺序（或你的正则替换逻辑未保留顺序关联标识），要保证字符按原顺序拆分，推荐通过字符位置索引逐个提取字符，以下是两种可行方案：

方法一：利用generate_series生成位置索引（推荐） #

通过字符串长度生成从1到字符串长度的连续整数序列，按位置逐个截取单个字符，天然保证顺序：

with fake_data(id, ex_character) as (
    select * from values
        ('A', 'T70891'),
        ('B', 'RT9811111')
)
select 
    fd.id,
    substring(fd.ex_character, pos, 1) as value
from fake_data fd
cross join lateral generate_series(1, length(fd.ex_character)) as pos
order by fd.id, pos;

方法二：改进split_to_table并保留顺序标识 #

如果坚持使用split_to_table，可以先给每个字符绑定位置序号，拆分后按序号排序，最后剥离序号：

with fake_data(id, ex_character) as (
    select * from values
        ('A', 'T70891'),
        ('B', 'RT9811111')
)
select 
    fd.id,
    split_part(f.val, '|', 2) as value
from fake_data fd
cross join lateral split_to_table(
    regexp_replace(fd.ex_character, '(.)', row_number() over()||'|\\1', 'g'), 
    '|'
) as f(val)
where f.val != ''
order by fd.id, split_part(f.val, '|', 1)::int;

原方法无序的原因 #

原语句中的regexp_replace逻辑是给每个字符插入分隔符，但split_to_table拆分后没有绑定原字符的位置信息，数据库无法保证拆分结果的输出顺序与原字符串一致，尤其是在字符串较长或执行计划涉及并行处理时，顺序会被打乱。

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