MSCEWI2047

Like function does not support an argument with certain collation constraints.

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

Severity

Low

Description

The function behavior in Teradata supports the use of arguments with restrictions related to COLLATION, when converted SnowFlake may support only some or none.

For example, REGEXP_REPLACE supports 'de' but not 'fr'.

Example Code

Input Code:

CREATE TABLE table1
 (
	col1 CHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
	col2 CHAR(35) COLLATE MULTINATIONAL,
	col3 CHAR(35)
);

SELECT  * FROM table1 WHERE col1 LIKE 'sd';
SELECT (REGEXP_REPLACE(col2,'[^a-z0-9^()-/_& ]+','1', 1, 0, 'i' )) FROM table1;

Output Code:

CREATE TABLE DATAWAREHOUSE.PUBLIC.table1
(
col1 CHAR(35) COLLATE 'en-ci',
col2 CHAR(35) COLLATE MULTINATIONAL,
col3 CHAR(35)
);

SELECT
*
FROM DATAWAREHOUSE.PUBLIC.table1 WHERE --** MSC-WARNING - MSCEWI2047 - LIKE FUNCTION DOES NOT SUPPORT ARGUMENT WITH CERTAIN COLLATION CONSTRAINTS SUCH AS 'fr' **
 col1 LIKE 'sd' ;

SELECT
(--** MSC-WARNING - MSCEWI2047 - LIKE FUNCTION DOES NOT SUPPORT ARGUMENT WITH CERTAIN COLLATION CONSTRAINTS SUCH AS 'fr' **
REGEXP_REPLACE(col2,'[^a-z0-9^()-/_& ]+','1', 1, 0, 'i' ))
FROM DATAWAREHOUSE.PUBLIC.table1;

Note in the output code that the column col1 was migrated as COLLATE, this case is not supported by SnowFlake thus resulting in a warning.

Recommendations

• Consider changing the argument character type passed to the function.

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