0

In SQL Server I execute:

select PATINDEX('%\%[0123456789][\ ]%', N'\deftab1134\paperw12240\paperh20000\margl900\margt1440\margr540\margb1440\plain\f1\fs24 That is my report');
---
1

It is correct. I need the same function in PostgreSQL. I have found this function:

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

But it works incorrectly with the same parameters:

select helper2_patindex('%\%[0123456789][\ ]%',
'\deftab1134\paperw12240\paperh20000\margl900\margt1440\margr540\margb1440\plain\f1\fs24 That is my report');
----
87

What is incorrect? what can I fix?

12
  • Postresql has regular expressions. Why do you need something called patindex? Write a regex that matches what you want. Commented Apr 19, 2018 at 14:52
  • % is not a valid wildcard in a regular expression. Commented Apr 19, 2018 at 14:53
  • I am porting SQL Server db to PostgreSQL. The patindex returns index of first matching, I need this functionality Commented Apr 19, 2018 at 14:54
  • 2 a_horse_with_no_name: yes, patindex uses not RE, but the same syntax as a LIKE operator. Commented Apr 19, 2018 at 14:55
  • @OLeg as I said, PostgreSQL has regular expressions. In fact, the code you copied tries to make REGEXP_MATCHES work like the far more limited PATINDEX. What do you want to match though? ` index of first matching` doesn't say match. First match of what? Commented Apr 19, 2018 at 14:55

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.