SSC-EWI-TD0003

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

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.

Example Code

Teradata:

IN -> Teradata_01.sql

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;

Snowflake Scripting:

OUT -> Teradata_01.sql

CREATE TABLE collateTable (
	col1 VARCHAR(50) COLLATE 'en-ci' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
	TRIM(COLLATE(col1, ''), '0') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/!!!,
	LTRIM(col1, '  '),
	RTRIM(COLLATE(col1, ''), '0') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/!!!,
	LTRIM(COLLATE(col1, ''), '0') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/!!!,
	RTRIM(col1)
	FROM
	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.

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