PostgreSQL：不使用正则提取指定子串的优雅方案问询

需求明确 #

需要从order_name字段中提取满足以下条件的子串：

• 以437.开头
• 结束于该子串的最后一位数字
• 子串后续的文本中不含任何数字

已通过(I)LIKE实现但步骤繁琐，现提供更简洁的实现方案。

测试准备 #

测试表结构 #

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_name TEXT NOT NULL
);

测试数据 #

INSERT INTO orders (order_id, order_name) VALUES
(1, '437.123abc'),          -- 符合条件，期望提取 '437.123'
(2, '437.890'),             -- 符合条件，期望提取 '437.890'
(3, 'prefix437.567def'),    -- 符合条件，期望提取 '437.567'
(4, '437.12345abc678'),     -- 子串后含数字，不符合，返回 NULL
(5, 'randomtext'),          -- 无匹配，返回 NULL
(6, '437.abc123');          -- 437.后无连续数字，不符合，返回 NULL

PostgreSQL 10+ 实现方案 #

方案1：正则表达式（最简洁优雅） #

利用PostgreSQL的substring正则匹配功能，结合条件判断确保子串后无数字：

SELECT
    order_id,
    order_name,
    CASE
        -- 先验证整体格式：437. + 数字序列 + 非数字字符（可选）直到结尾
        WHEN order_name ~ '437\.\d+[^0-9]*$' 
        THEN substring(order_name FROM '437\.\d+')
        ELSE NULL
    END AS extracted_substring
FROM orders;

说明：

• 437\.：匹配字面量437.（正则中.需转义）
• \d+：匹配1个及以上连续数字
• [^0-9]*$：确保数字序列后只有非数字字符直到字符串末尾

方案2：无正则优化版（基于LIKE/字符串函数） #

若需避免正则，可通过字符串定位和替换函数实现：

SELECT
    order_id,
    order_name,
    CASE
        WHEN order_name LIKE '437.%' THEN
            substring(
                order_name,
                position('437.' IN order_name),
                -- 计算目标子串长度：437.的长度(4) + 后续连续数字的长度
                4 + length(regexp_replace(substring(order_name FROM position('437.' IN order_name) + 4), '[^0-9]', '', 'g'))
            )
        ELSE NULL
    END AS extracted_substring
FROM orders
-- 过滤掉437.之后仍有数字的情况
WHERE order_name NOT LIKE '%437.%[0-9]%' ESCAPE '[';

其他数据库兼容方案 #

MySQL/MariaDB #

SELECT
    order_id,
    order_name,
    CASE
        WHEN order_name REGEXP '437\\.[0-9]+[^0-9]*$' 
        THEN REGEXP_SUBSTR(order_name, '437\\.[0-9]+')
        ELSE NULL
    END AS extracted_substring
FROM orders;

SQL Server #

SELECT
    order_id,
    order_name,
    CASE
        WHEN order_name LIKE '%437.[0-9]%' AND NOT order_name LIKE '%437.[0-9]%[0-9]%'
        THEN SUBSTRING(
            order_name,
            CHARINDEX('437.', order_name),
            4 + PATINDEX('%[^0-9]%', SUBSTRING(order_name, CHARINDEX('437.', order_name)+4, LEN(order_name))) - 1
        )
        ELSE NULL
    END AS extracted_substring
FROM orders;

测试结果 #

内容的提问来源于stack exchange，提问作者Vérace