Priya Priya - 7 months ago 60
SQL Question

SQL patindex equivalent in PostgreSQL

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

Answer Source

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 functionality, you can write your "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, '%' ), '%', '.*?' ), '_', '.' ) || ')'
                    ) )[ 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