SQL patindex equivalent in PostgreSQL

Question

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

Postgres offers full regular expression support. It is much more powerful and more standard than SQL Server's `patindex()`. — Gordon Linoff, May 11 '17 at 11:52
There is no direct equivalent to `patindex` (although can easily write it). However, if you show us the original query, maybe there is a better alternative in Postgres (as Gordon said: Postgres' regular expression support is much more sophisticated then SQL Server's) — , May 11 '17 at 11:53
I want a Postgres function with the name and functionality of SQL "Patindex " function . I am converting SQL queries to Postgres equivalent, so if I encounter a function in SQl as Patindex, I want the same function to be available in Postgres also — Priya, May 11 '17 at 12:18

ncank · Accepted Answer · 2018-04-20T15:32:47.970

6

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

edited Apr 20 '18 at 15:32

answered May 11 '17 at 21:08

ncank

946
5
15

1

Not exactly right. In MS SQL patindex('t%', 'Test string') = 1 (case-insensitive), while in PostgreSQL it will be 4, because REGEXP_MATCHES() used there is case-sensitive. Add LOWER() to all arguments worked for me. – Evgeny Nozdrev Apr 13 '18 at 10:13
It works incorreclty on the same data in PG and SQL Server, REGEXP_MATCHES uses regular expressions but patindex uses LIKE patterns – ZedZip Apr 19 '18 at 14:52
1

@Евгений Ноздрев thanks for pointing the missing part. i didn't notice the problem. we can make the regexp functionality case-insensitive by using the 'i' flag. – ncank Apr 20 '18 at 15:34
@Oleg we are taking LIKE patterns and converting them to REGEXP patterns here. so it works. if you can provide a problematic use case, i'll be glad the inspect the problem – ncank Apr 20 '18 at 15:37
@EvgenyNozdrev In MSSQL case sensitivity is regulated by choice of character encoding - CI (case insensitive) or CS (case sensitive). Take a look at: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e0d8f98b2f50804fd5ec22bc81fefe44 . So I think that original answer was absolutely correct for postgresql – Alex Yu Sep 03 '21 at 05:40
Although this difference in behaviour of string functions should be noted. But it's the same for all string functions in PostgreSQL and MSSQL – Alex Yu Sep 03 '21 at 05:42

score 0 · Answer 2 · edited Jan 14 '22 at 20:04

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

score 0 · Answer 3 · answered Jan 09 '23 at 13:44

0

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

answered Jan 09 '23 at 13:44

Marcelo Diaz

11
2

SQL patindex equivalent in PostgreSQL

3 Answers3