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
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
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
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
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
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.
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
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
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
Last updated