CODEWITHSUNDEEP
sqlpostgresqlpatindex
Priya
Priya

Reputation: 1226

SQL patindex equivalent in PostgreSQL

I am in need of Postgres equivalent of the SQL function patindex

Upvotes: 6

Views: 13032

Answers (3)

Marcelo Diaz
Marcelo Diaz

Reputation: 11

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

Upvotes: 0

KoNEW
KoNEW

Reputation: 1

PATINDEX and POSIX regular expressions are not fully compatible. For my purpose I finished with such version:

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;
$$;

Upvotes: 0

ncank
ncank

Reputation: 956

There is no exact equivalent to SQL Server's PATINDEX function. You can use other string functions for your needs. Here is the docs: https://www.postgresql.org/docs/current/static/functions-string.html

But if you need exactly the same function, you can write a "wrapper" as shown below:

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;

Example:

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

2

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

6

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

0

Upvotes: 7

Related Questions