Postgresql query to MySQL refactor

Question

I have a Psotgresql query and I need it for MySQL, is it possible to refactor this code to work on MySQL?

CREATE FUNCTION patinde(pattern VARCHAR(12), expression VARCHAR(12) ) RETURNS INT
    SELECT
        COALESCE(
            STRPOS(
                 $2
                ,(
                    SELECT
                        ( REGEXP_LIKE(
                            $2
                            ,'(' || REPLACE( REPLACE( TRIM( $1, '%' ), '%', '.*?' ), '_', '.' ) || ')'
                        ) )[ 1 ]
                    LIMIT 1
                )
            )
            ,0
        )
    ;
    $BODY$ LANGUAGE 'sql' IMMUTABLE;

The query looks very close to what MySQL uses, but in MySQL you say `CONCAT(a, b)` instead of `a || b`. As for the way to define a stored function, there are differences. [Read this](https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html). — O. Jones, Sep 01 '22 at 19:30
Pipes || should also work in MySQL, just like CONCAT() works in PostgreSQL. — Frank Heikens, Sep 01 '22 at 21:11

score 0 · Answer 1 · answered Sep 01 '22 at 20:21

There are numerous differences between the stored routine language of PostgreSQL and MySQL — and every other RDBMS, actually.

Here's a version that I tested on MySQL 8.0 that I assume does what your PostgreSQL function does, to report the character position where the pattern matching starts, using LIKE wildcards.

DELIMITER $$
CREATE FUNCTION patinde(pattern VARCHAR(100), expression VARCHAR(100)) RETURNS INT DETERMINISTIC
BEGIN
  DECLARE regex VARCHAR(200);

  SET regex = REPLACE(REPLACE(TRIM(BOTH '%' FROM pattern), '%', '.*'), '_', '.');

  RETURN (SELECT REGEXP_INSTR(expression, regex));
END $$
DELIMITER ;

Note that this doesn't quite do what yours does, because MySQL does not support Perl-compatible regular expressions. For example, MySQL does not support the .*? ungreedy matching operation.

Also both your function and my function don't account for escaped LIKE wildcards like \% and \_.

Postgresql query to MySQL refactor

1 Answers1