SSC-FDM-OR0044

REGEXP_LIKE_UDF match parameter may not behave correctly

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

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 Oracle:

IN -> Oracle_01.sql

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

Output Code:

OUT -> Oracle_01.sql

SELECT last_name
FROM
hr.employees
WHERE
--** SSC-FDM-OR0044 - 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_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 [email protected]