MSCEWI3089

REGEXP_LIKE_UDF match parameter may not behave correctly

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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

• When the REGEXP_LIKEcondition 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.
• If you need more support, you can email us at [e-mail]