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;

Answer 1

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 \_.