MSCEWI3089
REGEXP_LIKE_UDF match parameter may not behave correctly
Low
This warning appears when the Oracle
REGEXP_LIKE
condition comes with the third parameter (match parameter). The reason to add the warning is that the REGEXP_LIKE_UDF
used to replace the REGEXP_LIKE
does not recognize all the characters used by the match parameter, so the result of the query in Snowflake may not be equivalent to Oracle.SELECT last_name
FROM hr.employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i')
ORDER BY last_name;
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;
- When the
REGEXP_LIKE
condition comes with one of the characters that are not supported by the user-defined function, maybe a possible solution is to change the regular expression in order to simulate the behavior of the missing character in the match parameter. To know more about the character not supported go to REGEXP_LIKE_UDF documentation.
Last modified 3mo ago