PostgreSQL中CHARINDEX()与PATINDEX()的等效实现及T-SQL迁移问题

核心问题说明 #

T-SQL 的 CHARINDEX(needle, haystack, start_pos) 支持从指定起始位置查找字符，但 PostgreSQL 的 POSITION() 函数不支持起始位置参数；同时 T-SQL 的 PATINDEX() 模糊匹配逻辑，也需要用 PostgreSQL 原生函数替代。

替代方案 #

1. 模拟带起始位置的字符查找

要实现从指定位置开始查找字符的效果，可结合 SUBSTRING() 和 STRPOS()：

-- 等效于 T-SQL: CHARINDEX('=', PARAMETRI, start_pos)
STRPOS(SUBSTRING(PARAMETRI FROM start_pos), '=') + start_pos - 1

• 先截取从 start_pos 开始的子串，再用 STRPOS() 查找目标字符位置，最后加上起始位置偏移量。
• 若未找到目标字符，STRPOS() 返回 0，最终结果为 start_pos -1，需用 CASE 处理这种边界情况。

2. 替代 PATINDEX 模糊匹配

T-SQL 的 PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI) 可替换为 PostgreSQL 的 REGEXP_POSITION()：

-- 等效于 T-SQL: PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI)
REGEXP_POSITION(PARAMETRI, 'ACCOUNTFILTERFST=')

• REGEXP_POSITION() 会返回第一个匹配正则表达式的位置，和 PATINDEX 效果一致。

3. 完整查询示例（模拟原逻辑）

假设原 T-SQL 查询为：

SELECT
    XXX.ID,
    SUBSTRING(XXX.PARAMETRI, 
              PATINDEX('%ACCOUNTFILTERFST=%', XXX.PARAMETRI) + LEN('ACCOUNTFILTERFST='),
              CHARINDEX(';', XXX.PARAMETRI, PATINDEX('%ACCOUNTFILTERFST=%', XXX.PARAMETRI)) 
              - (PATINDEX('%ACCOUNTFILTERFST=%', XXX.PARAMETRI) + LEN('ACCOUNTFILTERFST='))) AS AccountFilter,
    YYY.Name
FROM dbo.XXX
JOIN dbo.YYY ON XXX.YYYID = YYY.ID

对应的 PostgreSQL 等效查询：

WITH param_cte AS (
    SELECT
        xxx.id,
        xxx.parametri,
        yyy.name,
        REGEXP_POSITION(xxx.parametri, 'ACCOUNTFILTERFST=') AS filter_start,
        LENGTH('ACCOUNTFILTERFST=') AS filter_str_len
    FROM xxx
    JOIN yyy ON xxx.yyyid = yyy.id
)
SELECT
    id,
    CASE 
        WHEN filter_start > 0 THEN
            SUBSTRING(parametri FROM (filter_start + filter_str_len)
                      FOR (STRPOS(SUBSTRING(parametri FROM filter_start), ';') - filter_str_len))
        ELSE NULL
    END AS account_filter,
    name
FROM param_cte;

4. 更简洁的正则提取方案

如果你的目标是提取 ACCOUNTFILTERFST= 到下一个分号之间的内容，直接用正则捕获组会更高效：

SELECT
    xxx.id,
    (REGEXP_MATCHES(xxx.parametri, 'ACCOUNTFILTERFST=([^;]+)'))[1] AS account_filter,
    yyy.name
FROM xxx
JOIN yyy ON xxx.yyyid = yyy.id;

• ([^;]+) 表示捕获所有非分号的字符，直接提取目标值，无需手动计算位置。

注意事项 #

• PostgreSQL 的 LENGTH() 对应 T-SQL 的 LEN()，但 LEN() 忽略末尾空格，若需一致行为，可结合 TRIM()：CHAR_LENGTH(TRIM(trailing FROM parametri))。
• 若 PARAMETRI 中无匹配的 ACCOUNTFILTERFST=，REGEXP_POSITION() 返回 0，需用 CASE 或 WHERE 过滤无效记录。

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