SQL patindex equivalent in PostgreSQL

Question

我需要相当于SQL函数patindex的Postgres。

Answer 1

没有与SQL Server的PATINDEX函数完全相同的等价物。您可以使用其他字符串函数来满足自己的需要。以下是文档： https://www.postgresql.org/docs/current/static/functions-string.html

但是如果你需要完全相同的函数，你可以编写一个"包装器"，如下所示：

CREATE OR REPLACE FUNCTION "patindex"( "pattern" VARCHAR, "expression" VARCHAR ) RETURNS INT AS $BODY$
SELECT
    COALESCE(
        STRPOS(
             $2
            ,(
                SELECT
                    ( REGEXP_MATCHES(
                        $2
                        ,'(' || REPLACE( REPLACE( TRIM( $1, '%' ), '%', '.*?' ), '_', '.' ) || ')'
                        ,'i'
                    ) )[ 1 ]
                LIMIT 1
            )
        )
        ,0
    )
;
$BODY$ LANGUAGE 'sql' IMMUTABLE;

例：

SELECT patindex( '%e_t%', 'Test String' );

阿拉伯数字

SELECT patindex( '%S_r%', 'Test String' );

6

SELECT patindex( '%x%', 'Test String' );

0

Answer 2

PATINDEX 和 POSIX 正则表达式并不完全兼容。出于我的目的，我完成了这样的版本：

CREATE OR REPLACE FUNCTION _patexpr2regexp
    (pattern TEXT) RETURNS TEXT
AS
$$
DECLARE
    buffer         TEXT = '';
    matches        TEXT[];
    match_part     TEXT;
    pattern_part   TEXT;
    match_position INT;
BEGIN
    FOR matches IN SELECT regexp_matches(pattern, '(_|[%]|%|.)', 'g')
        LOOP
            -- parsing
            match_part := matches[1];
            match_position := position(match_part IN pattern);
            pattern_part := substring(pattern, 1, match_position + LENGTH(match_part) - 1);
            pattern := substring(pattern, LENGTH(pattern_part) + 1);
            -- replacements
            IF match_part = '%'
            THEN
                -- lookaround regex pattern
                pattern_part = replace(pattern_part, '%', '(?=.*)');
            END IF;
            IF match_part = '[%]'
            THEN
                -- % escape
                pattern_part = replace(pattern_part, '[%]', '%');
            END IF;
            IF match_part = '_'
            THEN
                -- MSSQL _ means anysymbol (dot in regexp)
                pattern_part = replace(pattern_part, '_', '.');
            END IF;
            IF match_part = '.'
            THEN
                -- actaul dot symbol should be escaped
                pattern_part = replace(pattern_part, '.', '.');
            END IF;
            buffer := buffer || pattern_part;
            RAISE NOTICE 'matches: % -> % -> % -> % | %', matches, match_part, pattern_part, pattern, buffer;
        END LOOP;
    RAISE NOTICE 'result buffer: %', buffer;
    RETURN buffer;
END;
$$ LANGUAGE plpgsql STRICT
                      IMMUTABLE
                      PARALLEL SAFE;

CREATE OR REPLACE FUNCTION "PATINDEX"
    (pattern VARCHAR,
     expression VARCHAR,
     flags VARCHAR = 'i' -- default behaviour is case insensitive
     ) RETURNS INT
AS
$$
WITH cte AS (
    SELECT (
        REGEXP_MATCH(
            expression,
            _patexpr2regexp(pattern),
            flags)
        )[1] AS m
)
SELECT COALESCE(position(m IN expression), 0)
FROM cte;
$$ LANGUAGE sql STRICT
                  IMMUTABLE
                  PARALLEL SAFE;
-- checks
DO
$$
    BEGIN
        ASSERT "PATINDEX"('%.%', 'file.bat ') = 5;
        ASSERT "PATINDEX"('%.%', 'file.bat.as1   ') = 5;
        ASSERT "PATINDEX"('%.%', 'fileas') = 0;
        ASSERT "PATINDEX"(NULL, 'abc') IS NULL;
        ASSERT "PATINDEX"('abc', NULL) IS NULL;
        ASSERT "PATINDEX"('abc', 'abc') = 1;
        ASSERT "PATINDEX"('abc', 'cba   abc') = 7;
        -- TODO: not recognize properly
        -- ASSERT "PATINDEX"( '%_1_%[0-9][%]', '123 21234%') = 5;
    END;
$$;

Answer 3

[local]:5433 postgres@postgres=# select strpos('SQL Pattern Index','ern');
 strpos 
--------
      9
(1 row)