MSCEWI2003

Collation not supported in trim functions, add original collation to function result to preserve it.

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

Severity

Low

Description

In Snowflake, trim functions (LTRIM, RTRIM, or TRIM) do not support collation unless the characters to trim are empty or white space characters.

If SnowConvert detects a LTRIM, RTRIM or TRIM LEADING, TRAILING, OR BOTH function with the scenario mentioned above, the COLLATE function will be automatically generated to create a copy without collation of the input column. This EWI is generated to point out that the column collation was removed before the trim function, meaning the result of the function will not have collation and that this may change the results of further comparisons using the result.

Code Example

Input Code:

CREATE TABLE collateTable (
	col1 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
);

SELECT
TRIM(BOTH '0' FROM col1),
TRIM(LEADING '  ' FROM col1),
TRIM(TRAILING '0' FROM col1),
LTRIM(col1, '0'),
RTRIM(col1)
FROM collateTable;

Output Code:

CREATE TABLE PUBLIC.collateTable (
	col1 VARCHAR(50) COLLATE 'en-ci'
);

 SELECT
 TRIM(COLLATE(col1, ''), '0') /*** MSC-WARNING - MSCEWI2003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/,
 LTRIM(col1, ' '),
 RTRIM(COLLATE(col1, ''), '0') /*** MSC-WARNING - MSCEWI2003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/,
 LTRIM(COLLATE(col1, ''), '0') /*** MSC-WARNING - MSCEWI2003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/,
 RTRIM(col1)
 FROM
 PUBLIC.collateTable;

Recommendations

To avoid functional differences during comparisons, please add the original collation of the column to the TRIM function result string, this can be achieved using the COLLATE function and specifying the original column collation as the second argument, this argument has to be a literal string with the collation value.

PreviousMSCEWI2002 NextMSCEWI2004

Last updated 1 year ago

CREATE TABLE collateTable ( col1 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC ); SELECT TRIM(BOTH '0' FROM col1), TRIM(LEADING ' ' FROM col1), TRIM(TRAILING '0' FROM col1), LTRIM(col1, '0'), RTRIM(col1) FROM collateTable;

CREATE TABLE PUBLIC.collateTable ( col1 VARCHAR(50) COLLATE 'en-ci' ); SELECT TRIM(COLLATE(col1, ''), '0') /*** MSC-WARNING - MSCEWI2003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/, LTRIM(col1, ' '), RTRIM(COLLATE(col1, ''), '0') /*** MSC-WARNING - MSCEWI2003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/, LTRIM(COLLATE(col1, ''), '0') /*** MSC-WARNING - MSCEWI2003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/, RTRIM(col1) FROM PUBLIC.collateTable;