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

Teradata

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');

Snowflake

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE employees (
    employee_id INTEGER NOT NULL,
    first_name VARCHAR(50) COLLATE 'en-ci',
    last_name VARCHAR(50),
    department VARCHAR(50) COLLATE 'en-ci'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "11/01/2024",  "domain": "test" }}'
;

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 OR REPLACE TABLE departments (
    department_id INTEGER NOT NULL,
    department_name VARCHAR(50) COLLATE 'en-ci',
    location VARCHAR(100),
       PRIMARY KEY (department_id)
   )
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "11/01/2024",  "domain": "test" }}'
;

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

Query

SELECT *
FROM employees
WHERE first_name = 'GEorge ';

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
1	George	Snow	Sales
6	GEORGE		sales

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
1	George	Snow	Sales
6	GEORGE		sales

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

Teradata

Query

SELECT *
FROM employees
WHERE last_name = 'SNOW ';

Output

employee_id	first_name	last_name	department
3	WIlle	SNOW	Human resources
2	John	SNOW	Engineering

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
3	WIlle	SNOW	Human resources
2	John	SNOW	Engineering

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

Notice that the following queries

• SELECT * FROM employees WHERE first_name = 'JOHN ' (CASESPECIFIC)

• SELECT * FROM employees WHERE first_name (CASESPECIFIC) = 'JOHN '

will return the same values.

Teradata

Query

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

Output

employee_id	first_name	last_name	department
9	JOHN	SnoW	IT
10	JOHN	snow	Finance

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
9	JOHN	SnoW	IT
10	JOHN	snow	Finance

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

CAST to a column on the left side of the comparison has priority.

For example:

• SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC) = 'snoW'; will return 5 rows.

• SELECT * FROM employees WHERE last_name = 'snoW' (NOT CASESPECIFIC); will return 0 rows with this setup data.

Teradata

Query

SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC)  = 'snoW' ;

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
2	John	SNOW	Engineering
3	WIlle	SNOW	Human resources
4	Marco	SnoW	EngineerinG
10	JOHN	snow	Finance

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
2	John	SNOW	Engineering
3	WIlle	SNOW	Human resources
4	Marco	SnoW	EngineerinG
10	JOHN	snow	Finance

LIKE operation

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

Teradata

Query

SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
6	GEORGE		sales

Snowflake

Query

SELECT
   *
FROM
   employees
WHERE
   RTRIM(first_name) LIKE RTRIM('GeorgE');

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
6	GEORGE		sales

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

Teradata

Query

SELECT *
FROM employees
WHERE last_name LIKE 'Snow';

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

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

Teradata

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

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

Teradata

Query

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

Output

employee_id	first_name	last_name	department
3	WIlle	SNOW	Human resources
2	John	SNOW	Engineering

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
3	WIlle	SNOW	Human resources
2	John	SNOW	Engineering

IN Operation

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

Teradata

Query

IN -> Teradata_08.sql

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
1	George	Snow	Sales
6	GEORGE		sales

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
1	George	Snow	Sales
6	GEORGE		sales

Case 2: Column constraint is not defined and database mode is TERA Mode

In Tera mode, not defined case specification means NOT CASESPECIFIC.

Teradata

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
5	Mary		SaleS
6	GEORGE		sales
7	GEORGE		salEs
8	GeorgE		SalEs

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
5	Mary		SaleS
6	GEORGE		sales
7	GEORGE		salEs
8	GeorgE		SalEs

Case 3: Column constraint is CASESPECIFIC and database mode is TERA Mode

Teradata

Query

SELECT *
FROM employees
WHERE last_name IN ('SNOW   ');

Output

employee_id	first_name	last_name	department
3	WIlle	SNOW	Human resources
2	John	SNOW	Engineering

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
3	WIlle	SNOW	Human resources
2	John	SNOW	Engineering

ORDER BY clause

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

Teradata

Query

SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;

Output

employee_id	first_name
1	George
2	John
3	WIlle
4	Marco
5	Mary
6	GEORGE
7	GEORGE
8	GeorgE
9	JOHN
10	JOHN

Snowflake

Query

SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;

Output

employee_id	first_name
1	George
2	John
3	WIlle
4	Marco
5	Mary
6	GEORGE
7	GEORGE
8	GeorgE
9	JOHN
10	JOHN

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

Teradata

Query

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;

Output

employee_id	last_name
1	Snow
2	SNOW
3	SNOW
4	SnoW
5
6
7
8
9	SnoW
10	snow

Snowflake

Query

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;

Output

employee_id	last_name
1	Snow
2	SNOW
3	SNOW
4	SnoW
5
6
7
8
9	SnoW
10	snow

GROUP BY clause

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

Teradata

Query

SELECT first_name
FROM employees
GROUP BY first_name;

Output

first_name
Mary
GeorgE
WIlle
JOHN
Marco
GEORGE

Snowflake

Case specification in output may vary depending on the number of columns selected.

Query

SELECT
   first_name
FROM
   employees
GROUP BY first_name;

Output

first_name
John
Marco
George
GeorgE
WIlle
Mary

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

Teradata

Query

SELECT last_name
FROM employees
GROUP BY last_name;

Output

last_name
SnoW
SNOW
SnoW
Snow
snow

Snowflake

Query

SELECT
   last_name
FROM
   employees
GROUP BY last_name;

Output

last_name
SnoW
SNOW
SnoW
Snow
snow

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

Case specification in output may vary depending on the number of columns selected. This is also related to the GROUP BY clause.

Query

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

Output

employee_id	first_name
7	GEORGE
1	George
6	GEORGE

Snowflake

Query

SELECT
  employee_id,
  first_name
FROM
  employees
GROUP BY employee_id, first_name
HAVING
   RTRIM(first_name) = RTRIM('George  ');

Output

employee_id	first_name
7	GEORGE
1	George
6	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

Query

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 = '';

Output

first_name	last_name	department_full_name
GEORGE		GLOBAL SALES
Mary		Other
GeorgE		Other
GEORGE		GLOBAL SALES

Snowflake

Query

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('');

Output

first_name	last_name	department_full_name
GEORGE		GLOBAL SALES
Mary		Other
GeorgE		Other
GEORGE		GLOBAL SALES

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

Query

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;

Output

employee_id	first_name	last_name	department_name
1	George	Snow	Sales
3	WIlle	SNOW	Human Resources
5	Mary		Sales
6	GEORGE		Sales
7	GEORGE		Sales
8	GeorgE		Sales
10	JOHN	snow	Finance

Snowflake

Query

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);

Output

employee_id	first_name	last_name	department_name
1	George	Snow	Sales
3	WIlle	SNOW	Human Resources
5	Mary		Sales
6	GEORGE		Sales
7	GEORGE		Sales
8	GeorgE		Sales
10	JOHN	snow	Finance

Related EWIs

SSC-EWI-TD0007: GROUP BY REQUIRED COLLATE FOR CASE INSENSITIVE COLUMNS

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