MSCEWI3089

REGEXP_LIKE_UDF match parameter may not behave correctly

Severity

Low

Description

This warning appears when the Oracle REGEXP_LIKEcondition comes with the third parameter (match parameter). The reason to add the warning is that the REGEXP_LIKE_UDFused to replace the REGEXP_LIKEdoes not recognize all the characters used by the match parameter, so the result of the query in Snowflake may not be equivalent to Oracle.

Example Code

Input Code:

SELECT last_name
FROM hr.employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i')
ORDER BY last_name;

Output Code:

SELECT last_name
FROM hr.employees
WHERE /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'REGEXP_LIKE_UDF' INSERTED. ***/
 /*** MSC-WARNING - MSCEWI3089 - REGEXP_LIKE_UDF MATCH PARAMETER MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE. ***/
 PUBLIC.REGEXP_LIKE_UDF(last_name, '([aeiou])\\1', 'i')
ORDER BY last_name;

Recommendations