MSCEWI2021

Unsupported use of parameter "match_arg" detected. Values 'l' and 'x' are not supported in Snowflake. This parameter is used in some regular expressions functions.

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

Severity

Low

Description

In Teradata functions like REGEX_SUBSTR, REGEX_REPLACE, or REGEX_INSTR have a parameter called "match_arg", a character argument with the following valid values:

• 'i': case-insensitive matching.

• 'c': case sensitive matching.

• 'n': the period character (match any character) can match the newline character.

• 'm': source string is treated as multiple lines instead of as a single line.

• 'l': if source_string exceeds the current maximum allowed source_string size (currently 16 MB), a NULL is returned instead of an error.

• 'x': ignore whitespace.

The argument can contain more than one character.

In Snowflake, the equivalent argument for these functions is regexp_parameters.A string of one or more characters that specifies the regular expression parameters used for searching for matches. The supported values are:

• c: case-sensitive.

• i: case-insensitive.

• m: multi-line mode.

• e: extract sub-matches.

• s: the ‘.’ the wildcard also matches the newline character as well.

As it can be seen, values 'i', 'c', 'm' are the same in both languages, and the 'n' value in Teradata is mapped to 's'. However, values 'l', 'x' don't have an equivalent counterpart. It is in these cases that SnowConvert adds this warning.

Input Code:

SELECT REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'i'), 
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'c'),
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'm'),
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'n'),
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'l'),
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'x');

Output Code:

SELECT --** MSC-WARNING - MSCEWI2020 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'i'), 
       --** MSC-WARNING - MSCEWI2020 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'c'),
       --** MSC-WARNING - MSCEWI2020 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 'm'),
       --** MSC-WARNING - MSCEWI2020 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1, 's'),
       --** MSC-WARNING - MSCEWI2020 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
       --** MSC-WARNING - MSCEWI2021 - VALUE 'l' FOR PARAMETER 'match_arg' IS NOT SUPPORTED IN SNOWFLAKE **
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1),
       --** MSC-WARNING - MSCEWI2020 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
       --** MSC-WARNING - MSCEWI2021 - VALUE 'x' FOR PARAMETER 'match_arg' IS NOT SUPPORTED IN SNOWFLAKE **
       REGEXP_SUBSTR('Chip Chop','ch(i|o)p', 1, 1);

Note that warning MSCEWI2020 is also added for each use of this function.

Recommendations

• If you need more support, you can email us at [email protected]