SSC-FDM-PG0011

The use of the COLLATE column constraint has been disabled for this pattern-matching condition

Description

This message is added when a pattern-matching condition uses arguments with COLLATE specifications, as they are not currently supported in Snowflake's regular expression function. Consequently, the COLLATE clause must be disabled to use this function, which may result in differences in the results.

Code Example

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE CASE_INSENSITIVE,
col2 VARCHAR(30) COLLATE CASE_SENSITIVE);

INSERT INTO collateTable values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
col1 SIMILAR TO 'Hello%' as ci,
col2 SIMILAR TO 'Hello%' as cs
FROM collateTable;

Results

CI	CS
TRUE	FALSE

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE 'en-ci',
col2 VARCHAR(30) COLLATE 'en-cs'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}';

INSERT INTO collateTable
values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col1, ''), 'Hello.*', 's') as ci,
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col2, ''), 'Hello.*', 's') as cs
FROM
collateTable;

Results

CI	CS
FALSE	FALSE

Recommendations

• If you require equivalence for these scenarios, you can manually add the following parameters to the function to achieve functional equivalence:

  Parameter	Description
  c	Case-sensitive matching
  i	Case-insensitive matching

• For more information please refer to the following link.

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