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?

ZedZip
  • 5,794
  • 15
  • 66
  • 119
  • Postresql has regular expressions. Why do you need something called `patindex`? Write a regex that matches what you want. – Panagiotis Kanavos Apr 19 '18 at 14:52
  • `%` is not a valid wildcard in a regular expression. –  Apr 19 '18 at 14:53
  • I am porting SQL Server db to PostgreSQL. The patindex returns index of first matching, I need this functionality – ZedZip Apr 19 '18 at 14:54
  • 2 a_horse_with_no_name: yes, patindex uses not RE, but the same syntax as a LIKE operator. – ZedZip Apr 19 '18 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? – Panagiotis Kanavos Apr 19 '18 at 14:55
  • @OLeg you are *porting*. You won't find identical functionality. In some cases, PostgreSQL offers a lot more. This is one of them. Instead of trying to replicate the *workaround* you used in SQL Server, use the improved functionality – Panagiotis Kanavos Apr 19 '18 at 14:56
  • @Oleg in other cases though PostgreSQL lacks important functionality, like database-level backups. Or change tracking, or the `SEQUENCE` syntax (it has custom syntax for this). Don't waste time trying to replicate PATINDEX, you'll need it for other, serious issues – Panagiotis Kanavos Apr 19 '18 at 14:58
  • Yes, you are right, but in any case: now I need a function which returns and index of first matching: '%\%[0123456789][\ ]%' = somewhere in a string: \ anychars number\ any chars – ZedZip Apr 19 '18 at 14:58
  • @Oleg this would match anything in that string. In any case, *why* do you want the index? Don't you want the actual matching part? `regexp_matches` will return *all* matches as an array. If you want to find whether something matches a regex or not, use [the `~` operator ](https://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP) don't try to find indexes. I suspect the regex you want is `\\\d+[\\ ]` – Panagiotis Kanavos Apr 19 '18 at 15:04
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/169354/discussion-between-oleg-and-panagiotis-kanavos). – ZedZip Apr 19 '18 at 15:08
  • https://stackoverflow.com/q/21104366/330315 but with a true regex you can **not** use `%` as a wildcard. –  Apr 19 '18 at 15:39
  • Thank you all guys! yes, with RE my function works: I have used this expression '\\([a-z]{1,32})(-?\d{1,10})?[ ]?' – ZedZip Apr 20 '18 at 12:22

0 Answers0