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:
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;
CI
CS
TRUE
FALSE
Output Code:
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;
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.
