从PostgreSQL表提取SLA信息：文本匹配与数值提取需求

针对给定的test表结构与数据，以下SQL语句可实现需求：判断描述字段是否包含指定SLA文本，标记"SLA text"为Yes/No；若为Yes则提取对应天数，含优先级时效时以"缩短天数/普通天数"格式输出。

SELECT 
    id,
    description,
    -- 判断是否包含SLA文本（兼容带非断空格的情况）
    CASE 
        WHEN description ~* 'The(\ )?average processing time for this type of request is' THEN 'Yes'
        ELSE 'No'
    END AS "SLA text",
    -- 提取天数，区分有无优先级时效的情况
    CASE 
        WHEN description ~* 'The(\ )?average processing time for this type of request is (\d+) days?\.' THEN
            CASE 
                -- 存在优先级缩短时效时，拼接成"缩短天数/普通天数"格式
                WHEN description ~* 'This delay is reduced to (\d+) day? for requests with high or critical priority\.' THEN
                    CONCAT(
                        (regexp_match(description, 'This delay is reduced to (\d+) day? for requests with high or critical priority\.', 'i'))[1],
                        '/',
                        (regexp_match(replace(description, ' ', ' '), 'The average processing time for this type of request is (\d+) days?\.', 'i'))[1]
                    )
                -- 仅普通时效时，直接提取天数
                ELSE
                    (regexp_match(replace(description, ' ', ' '), 'The average processing time for this type of request is (\d+) days?\.', 'i'))[1]
            END
        ELSE NULL
    END AS days
FROM test;

关键逻辑说明 #

• 使用~*进行不区分大小写的正则匹配，通过(\ )?兼容文本中存在的非断空格（ ）
• 利用regexp_match提取数字内容，先替换非断空格为普通空格，确保正则匹配准确性
• 嵌套CASE语句分层处理：先判断是否存在SLA文本，再判断是否包含优先级时效规则，输出对应格式的天数

查询结果 #

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