Comment on page
MSCEWI2003
Collation not supported in trim functions, add original collation to function result to preserve it.
Low
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.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;
- To avoid functional differences during comparisons, please add the original collation of the column to the
TRIMfunction result string, this can be achieved using theCOLLATEfunction and specifying the original column collation as the second argument, this argument has to be a literal string with the collation value.