SnowflakeDocumentation Home

ANSI Mode For Strings Comparison - COLLATE

This section defines the translation specification for a string in ANSI mode with the use of COLLATE.

Description

ANSI mode for string comparison and COLLATE usage

The ANSI mode string comparison will apply the COLLATE constraint to the columns or statements as required. The default case specification trim behavior may be taken into account.

Notice that in Teradata, the default case specification is 'CASESPECIFIC', the same default as in Snowflake 'case-sensitive'. Thus, these cases will not be translated with a COLLATE because it will be redundant.

Sample Source Patterns

Setup data

IN -> Teradata_01.sql
CREATE TABLE employees (
    employee_id INTEGER NOT NULL,
    first_name VARCHAR(50) NOT CASESPECIFIC,
    last_name VARCHAR(50) CASESPECIFIC,
    department VARCHAR(50)
);

INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (3, 'WIlle', 'SNOW', 'Human resources   ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (4, 'Marco', 'SnoW   ', 'EngineerinG');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (5, 'Mary', '   ', 'SaleS  ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (6, 'GEORGE', '  ', 'sales  ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (7, 'GEORGE   ', '  ', 'salEs  ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (8, '    GeorgE   ', '  ', 'SalEs  ');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (9, 'JOHN', '   SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (10, 'JOHN    ', 'snow', 'Finance   ');

CREATE TABLE departments (
    department_id INTEGER NOT NULL,
    department_name VARCHAR(50) NOT CASESPECIFIC,
    location VARCHAR(100) CASESPECIFIC,
    PRIMARY KEY (department_id)
);


INSERT INTO departments (department_id, department_name, location) VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location) VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location) VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location) VALUES (104, 'Finance', 'Boston');

Comparison operation

Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT *
FROM employees
WHERE first_name = 'GEorge ';

Snowflake

SELECT
    *
FROM
    employees
WHERE
    COLLATE(first_name, 'en-cs-rtrim') = RTRIM('George');

Case 2: Column constraint is CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT *
FROM employees
WHERE last_name = 'SNOW ';

Snowflake

SELECT
 *
FROM
 employees
WHERE
 RTRIM(last_name) = RTRIM('SNOW ');

Case 3: CAST NOT CASESPECIFIC column to CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT * FROM employees WHERE first_name = 'George   ' (CASESPECIFIC);

Snowflake

COLLATE 'en-cs' is required for functional equivalence.

SELECT
    *
FROM
    employees
WHERE
    COLLATE(first_name, 'en-cs-rtrim') = 'George   ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;

Case 4: CAST CASESPECIFIC column to NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT * FROM employees WHERE first_name = 'GEorge   ' (NOT CASESPECIFIC) ;

Snowflake

SELECT
   *
FROM
   employees
WHERE
   RTRIM(first_name) = RTRIM('GEorge   ' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);

Case 5: CAST NOT CASESPECIFIC column to NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT * FROM employees WHERE first_name (NOT CASESPECIFIC)  = 'George    ';

Snowflake

It requires COLLATE.

SELECT
   * 
FROM
   employees
WHERE
   COLLATE(first_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-cs-rtrim') = 'George    ';

LIKE operation

Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT *
FROM employees
WHERE first_name LIKE 'George';

Snowflake

SELECT *
FROM employees
WHERE COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';

Case 2: Column constraint is CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT *
FROM employees
WHERE last_name LIKE 'Snow';

Snowflake

SELECT *
FROM employees
WHERE RTRIM(last_name) LIKE RTRIM('Snow');

Case 3: CAST NOT CASESPECIFIC column to CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT *
FROM employees
WHERE first_name LIKE 'Mary' (CASESPECIFIC);

Snowflake

SELECT
   *
FROM
   employees
WHERE
   COLLATE(first_name, 'en-cs-rtrim') LIKE 'Mary' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;

Case 4: CAST CASESPECIFC column to NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);

Snowflake

SELECT
   *
FROM
   employees
WHERE
   RTRIM(last_name) LIKE RTRIM('SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);

IN Operation

Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT *
FROM employees
WHERE first_name IN ('George   ');

Snowflake

This case requires COLLATE(column_name, 'en-cs-rtrim') 

SELECT
   *
FROM
   employees
WHERE
   RTRIM(first_name) IN (COLLATE('George   ', 'en-cs-rtrim'));

Case 2: Column constraint is CASESPECIFIC and database mode is ANSI Mode

Teradata

For this case, the column does not have a column constraint, but the default constraint in Teradata ANSI mode is CASESPECIFIC.

SELECT *
FROM employees
WHERE department IN ('EngineerinG    ');

Snowflake

SELECT
   *
FROM
   employees
WHERE
   RTRIM(department) IN (RTRIM('EngineerinG    '));

ORDER BY clause

Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT first_name
FROM employees
ORDER BY first_name;

Snowflake

Please review FDM. Pending to add.

SELECT
   first_name
FROM
   employees
ORDER BY first_name;

Case 2: Column constraint is CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT last_name
FROM employees
ORDER BY last_name;

Snowflake

SELECT
   last_name
FROM
   employees
ORDER BY last_name;

GROUP BY clause

Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT first_name
FROM employees
GROUP BY first_name;

Snowflake

The case or order may differ in output.

RTRIM is required in selected columns.

  SELECT
   first_name
  FROM
   employees
  GROUP BY first_name;

Case 2: Column constraint is CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT last_name
FROM employees
GROUP BY last_name;

Snowflake

The order may differ.

SELECT
   last_name
  FROM
   employees
  GROUP BY last_name;

HAVING clause

The HAVING clause will use the patterns in:

  • Evaluation operations.

    • For example: =, !=, <, >.

  • LIKE operation.

  • IN Operation.

  • CAST to evaluation operation.

  • CAST to LIKE operation.

The following sample showcases a pattern with evaluation operation.

Sample: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'Mary';

Snowflake

SELECT
  first_name
FROM
  employees
GROUP BY first_name
HAVING
   COLLATE(first_name, 'en-cs-rtrim') = 'Mary';

CASE WHEN statement

The CASE WHEN statement will use the patterns described in:

  • Evaluation operations.

    • For example: =, !=, <, >.

  • LIKE operation.

  • IN Operation.

  • CAST to evaluation operation.

  • CAST to LIKE operation.

The following sample showcases a pattern with evaluation operation.

Teradata

SELECT first_name,
      last_name,
      CASE
          WHEN department = 'EngineerinG' THEN 'Information Technology'
          WHEN first_name = '    GeorgE   ' THEN 'GLOBAL SALES'
          ELSE 'Other'
      END AS department_full_name
FROM employees
WHERE last_name = '';

Snowflake

   SELECT
   first_name,
   last_name,
   CASE
         WHEN RTRIM(department) = RTRIM('EngineerinG')
            THEN 'Information Technology'
         WHEN COLLATE(first_name, 'en-cs-rtrim')  = '    GeorgE   '
            THEN 'GLOBAL SALES'
       ELSE 'Other'
   END AS department_full_name
FROM
   employees
WHERE RTRIM(last_name) = RTRIM('');

JOIN clause

Simple scenarios with evaluation operations are supported.

The JOIN statement will use the patterns described in:

  • Evaluation operations.

    • For example: =, !=, <, >.

  • LIKE operation.

  • IN Operation.

  • CAST to evaluation operation.

  • CAST to LIKE operation.

The following sample showcases a pattern with evaluation operation.

Sample: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM
    employees e
JOIN
    departments d
ON
    e.department = d.department_name;

Snowflake

d.department_name is NOT CASESPECIFIC, so it requires COLLATE.

   SELECT
   e.employee_id,
   e.first_name,
   e.last_name,
   d.department_name
FROM
   employees e
JOIN
   departments d
ON COLLATE(e.department, 'en-cs-rtrim') = d.department_name;

Related EWIs

SSC-EWI-TD0007: GROUP BY IS NOT EQUIVALENT IN TERADATA MODE

SC-FDM-TD0032 : [NOT] CASESPECIFIC CLAUSE WAS REMOVED

PreviousANSI Mode For Strings Comparison - NO COLLATENextSQL Translation Reference

Last updated