SSC-FDM-TD0016

Value 'l' for parameter 'match_arg' is not supported in Snowflake

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 (only affects the pattern string).

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.

For the 'x' value, the functionallity is replicated by generating a call to the REGEXP_REPLACE function. However, the 'l' parameter can not be replicated so this warning is generated for these cases.

Input Code:

IN -> Teradata_01.sql

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:

OUT -> Teradata_01.sql

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, 's'),
       --** SSC-FDM-TD0016 - VALUE 'l' FOR PARAMETER 'match_arg' IS NOT SUPPORTED IN SNOWFLAKE **
       REGEXP_SUBSTR('Chip Chop', 'ch(i|o)p', 1, 1),
       REGEXP_SUBSTR('Chip Chop', 'ch(i|o)p', 1, 1);

Recommendations

• If you need more support, you can email us at [e-mail]