PostgreSQL如何按规则将字符串拆分为指定行列的结构化表格

实现逻辑 #

拆分核心按3步走：

• 先将原始字符串按空格切分为带位置序号的独立词元
• 以所有纯数字词元作为行分界标记，每个数字对应一行结果
• 逐行提取字段：数字本身存入col3，数字紧邻的前一个词元存入col2，上一个数字之后、当前col2之前的所有词元用空格拼接存入col1

可直接运行的SQL代码 #

WITH
-- 传入待拆分的原始字符串
raw_input AS (
    SELECT 'ab bc 123 cd de ef 232' AS target_str
),
-- 按空格拆分字符串，给每个词元加位置序号
tokenized AS (
    SELECT
        ordinality AS token_pos,
        token
    FROM raw_input,
    unnest(string_to_array(target_str, ' ')) WITH ORDINALITY AS token
),
-- 识别所有数字词元（即col3的值），给每行分组标记
num_markers AS (
    SELECT
        token_pos AS num_pos,
        token AS col3,
        ROW_NUMBER() OVER (ORDER BY token_pos) AS group_id
    FROM tokenized
    WHERE token ~ '^[0-9]+$'
)
-- 逐组拼接三个字段
SELECT
    TRIM(
        COALESCE(
            STRING_AGG(CASE WHEN t.token_pos < nm.num_pos -1 THEN t.token END, ' ' ORDER BY t.token_pos),
            ''
        )
    ) AS col1,
    MAX(CASE WHEN t.token_pos = nm.num_pos -1 THEN t.token END) AS col2,
    nm.col3
FROM num_markers nm
LEFT JOIN tokenized t
-- 关联当前分组对应区间内的所有词元：上一个数字的位置+1 到 当前数字位置-1
ON t.token_pos > COALESCE((SELECT num_pos FROM num_markers WHERE group_id = nm.group_id -1), 0)
AND t.token_pos < nm.num_pos
GROUP BY nm.group_id, nm.col3
ORDER BY nm.group_id;

运行结果 #

执行上述SQL后会得到和预期完全一致的输出：

适配说明 #

如果需要处理其他同格式字符串，只需要修改raw_input CTE里的target_str值即可；如果数字可能包含小数、负号，只需要调整num_markers里的正则匹配规则适配对应数字格式。

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