ANSI Mode For Strings Comparison - NO COLLATE
This section defines the translation specification for a string in ANSI mode without the use of COLLATE.
Description
ANSI mode for string comparison and NO COLATE usages.
The ANSI mode string comparison without the use of COLLATE will apply RTRIM and UPPER as needed. The default case specification trim behavior may be taken into account, so if a column does not have a case specification in Teradata ANSI mode, Teradata will have as default CASESPECIFIC.
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
RTRIM(first_name) = 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
The (CASESPECIFIC) overwrite the column constraint in the table definition.
Teradata
SELECT * FROM employees WHERE first_name = 'GEorge ' (CASESPECIFIC);Snowflake
SELECT * FROM workers
WHERE RTRIM(first_name) = RTRIM(UPPER('GEorge '));Case 4: CAST CASESPECIFIC column to NOT CASESPECIFIC and database mode is ANSI Mode
Teradata
SELECT * FROM employees
WHERE last_name = 'SnoW ' (NOT CASESPECIFIC) ;Snowflake
SELECT * FROM employees
WHERE RTRIM(last_name) = RTRIM('SnoW ');LIKE operation
Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode
Teradata
SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';Snowflake
SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';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 last_name LIKE 'Snow';Case 3: CAST NOT CASESPECIFIC column to NOT CASESPECIFIC and database mode is ANSI Mode
Teradata
SELECT *
FROM employees
WHERE first_name LIKE 'George' (NOT CASESPECIFIC);Snowflake
SELECT
*
FROM
employees
WHERE
first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;Case 4: CAST CASESPECIFIC 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
last_name LIKE '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 (RTRIM('GEORGE '));Case 2: Column constraint is CASESPECIFIC and database mode is ANSI Mode
Teradata
SELECT *
FROM employees
WHERE department IN ('SaleS');Snowflake
SELECT *
FROM employees
WHERE RTRIM(department) IN (RTRIM('SaleS'));ORDER BY clause
Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode
Teradata
SELECT department_name
FROM departments
ORDER BY department_name;Snowflake
SELECT
department_name
FROM
departments
ORDER BY
UPPER(department_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
To ensure a functional equivalence, it is required to use the COLLATE expression.
Please review the SSC-EWI-TD0007 for more information.
Case 1: Column constraint is NOT CASESPECIFIC and database mode is ANSI Mode
Teradata
SELECT first_name
FROM employees
GROUP BY first_name;Snowflake
SELECT
first_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
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
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
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 = 'GEORGE';Snowflake
SELECT
first_name
FROM
employees
GROUP BY 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 = 'SaleS ' THEN 'GLOBAL SALES'
WHEN first_name = 'GEORGE ' THEN 'Department Full Name'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE last_name = ' ';Snowflake
SELECT
first_name,
last_name,
CASE
WHEN UPPER(RTRIM(department)) = UPPER(RTRIM('SaleS '))
THEN 'GLOBAL SALES'
WHEN UPPER(RTRIM(first_name)) = UPPER(RTRIM('GEORGE '))
THEN 'Department Full Name'
ELSE 'Other'
END AS department_full_name
FROM
employees
WHERE
UPPER(RTRIM( last_name)) = UPPER(RTRIM(' '));JOIN clause
Simple scenarios 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 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 RTRIM(e.department) = RTRIM(d.department_name);Related EWIs
SSC-EWI-TD0007: GROUP BY IS NOT EQUIVALENT IN TERADATA MODE
Last updated