TERA Mode For Strings Comparison - COLLATE
This section defines the translation specification for string in Tera mode with the use of COLLATE.
Description
Tera Mode for string comparison and COLLATE usage
The Tera 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. The default case specification in Teradata for TERA mode is NOT CASESPECIFIC
. Thus, the columns without case specification will have COLLATE('en-ci')
constraints.
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 TERA Mode
Teradata
SELECT *
FROM employees
WHERE first_name = 'GEorge ';
Snowflake
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('GEorge ');
Case 2: Column constraint is CASESPECIFIC and database mode is TERA 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 TERA Mode
Teradata
SELECT * FROM employees WHERE first_name = 'JOHN ' (CASESPECIFIC);
Snowflake
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') = 'JOHN ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
Case 4: CAST CASESPECIFIC column to NOT CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC) = 'snoW' ;
Snowflake
SELECT
*
FROM
employees
WHERE
COLLATE(last_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-ci-rtrim') = 'snoW' ;
LIKE operation
Case 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';
Snowflake
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) LIKE RTRIM('GeorgE');
Case 2: Column constraint is CASESPECIFIC and database mode is TERA 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 TERA Mode
Teradata
SELECT *
FROM employees
WHERE first_name LIKE 'George' (CASESPECIFIC);
Snowflake
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';
Case 4: CAST CASESPECIFIC column to NOT CASESPECIFIC and database mode is TERA 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 TERA Mode
Teradata
SELECT *
FROM employees
WHERE first_name IN ('George ');
Snowflake
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) IN (RTRIM('George '));
Case 2: Column constraint is not defined and database mode is TERA Mode
Teradata
SELECT *
FROM employees
WHERE department IN ('Sales ');
Snowflake
SELECT
*
FROM
employees
WHERE
RTRIM(department) IN (RTRIM('Sales '));
Case 3: Column constraint is CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT *
FROM employees
WHERE last_name IN ('SNOW ');
Snowflake
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) IN (RTRIM('SNOW '));
ORDER BY clause
Case 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
Snowflake
SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
Case 2: Column constraint is CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
Snowflake
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
GROUP BY clause
Case 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT first_name
FROM employees
GROUP BY first_name;
Snowflake
Case specification in output may vary depending on the number of columns selected.
SELECT
first_name
FROM
employees
GROUP BY first_name;
Case 2: Column constraint is CASESPECIFIC and database mode is TERA 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 TERA Mode
Teradata
SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'George ';
Snowflake
SELECT
employee_id,
first_name
FROM
employees
GROUP BY employee_id, first_name
HAVING
RTRIM(first_name) = RTRIM('George ');
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 RTRIM(first_name) = 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:
EvaluaComparisonComparisontion 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 TERA 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 RTRIM(e.department) = RTRIM(d.department_name);
Related EWIs
SSC-EWI-TD0007: GROUP BY REQUIRED COLLATE FOR CASE INSENSITIVE COLUMNS
SC-FDM-TD0032 : [NOT] CASESPECIFIC CLAUSE WAS REMOVED
Last updated