TERA Mode For Strings Comparison - NO COLLATE

This section defines the translation specification for string in Tera mode without using COLLATE.

Description

Tera Mode for string comparison and NO COLLATE usages

The Tera 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.

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),
    last_name VARCHAR(50),
    department VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/30/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),
    location VARCHAR(100),
       PRIMARY KEY (department_id)
   )
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/30/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

This example demonstrates the usage of a column set up as NOT CASESPECIFIC as it is a first_name column. Even when asking for the string 'GEorge', the query execution will retrieve results in Teradata because the case specification is not considered.

To emulate this scenario in Snowflake, there are implemented two functions: RTRIM(UPPER(string_evaluation)), UPPER is required in this scenario because the string does not review the case specification.

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(UPPER(first_name)) = RTRIM(UPPER('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

For this example, the column constraint is CASESPECIFIC, for which the example does not retrieve rows in Teradata because 'Snow' is not equal to 'SNOW'.

In Snowflake, the resulting migration points only to the use of the RTRIM function since the case specification is important.

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 CASESPECIFIC column to NOT CASESPECIFIC and database mode is TERA Mode

Teradata

The (CASESPECIFIC) overrides the column constraint in the table definition.

Query

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
6	GEORGE		sales

Snowflake

RTRIM is required on the left side, and RTRIM is required on the right side.

Query

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
6	GEORGE		sales

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

Teradata

Query

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

Output

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

Snowflake

Query

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

Output

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

Case 5: Blank spaces case. Column constraint is NOT CASESPECIFIC, database mode is TERA Mode, and using equal operation

Teradata

Query

SELECT *
FROM employees
WHERE last_name = '   ';

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
5	Mary		SaleS
8	GeorgE		SalEs
6	GEORGE		sales

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
5	Mary		SaleS
8	GeorgE		SalEs
6	GEORGE		sales

LIKE operation

This operation works differently from another one. Blank spaces must be the same quantity to retrieve information.

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

This example is expected to display one row because the case specification is not relevant.

In Snowflake, the migration uses the ILIKE operation. This performs a case-insensitive comparison.

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 first_name ILIKE '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

first_name	last_name	department
George	Snow	Sales
Jonh	Snow	Engineering

Snowflake

Query

SELECT *
FROM employees
WHERE last_name LIKE 'Snow';

Output

first_name	last_name	department
George	Snow	Sales
Jonh	Snow	Engineering

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

Teradata

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
6	GEORGE		sales

Snowflake

Query

SELECT
   *
FROM
   employees
WHERE 
   first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales
6	GEORGE		sales

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

This case requires the translation to ILIKE.

Teradata

Query

SELECT *
FROM employees
WHERE first_name LIKE 'GE%' (NOT CASESPECIFIC);

Output

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

Snowflake

Query

SELECT
   *
FROM
   employees
WHERE
   first_name ILIKE 'GE%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;

Output

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

IN Operation

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

Teradata

Query

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(UPPER(first_name)) IN (RTRIM(UPPER('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

For this example, the usage of the UPPER function is not required since, in the Teradata database, the case specification is relevant to the results.

Teradata

Query

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

Output

employee_id	first_name	last_name	department
4	Marco	SnoW	EngineerinG

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
4	Marco	SnoW	EngineerinG

ORDER BY clause

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

Notice that this output order can differ.

Teradata

Query

SELECT department
FROM employees
ORDER BY department;

Output

department
EngineerinG
Engineering
Finance
Human resources
IT
sales
SalEs
Sales
SaleS
salEs

Snowflake

Query

SELECT department
FROM employees
ORDER BY UPPER(department);

Output

department
EngineerinG
Engineering
Finance
Human resources
IT
sales
SalEs
Sales
SaleS
salEs

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

Notice that this output can differ in order.

Teradata

Query

SELECT last_name
FROM employees
ORDER BY last_name;

Output

last_name
SnoW
SNOW
SNOW
SnoW
Snow
snow

Snowflake

Query

SELECT last_name
FROM employees
ORDER BY last_name;

Output

last_name
SnoW
SNOW
SNOW
SnoW
Snow
snow

GROUP BY clause

Notice that this output can differ. To ensure a functional equivalence, it is required to use the COLLATE expression.

Please review the SSC-EWI-TD0007 for more information.

The following might be a workaround without collate:

SELECT RTRIM(UPPER(first_name))

FROM employees

GROUP BY RTRIM(UPPER(first_name));

About the column behavior

Please review the insertion of data in Snowflake. Snowflake does allow the insertion of values as 'GEORGE' and 'georges' without showing errors because the case specification is not bound explicitly with the column.

Assume a table and data as follows:

CREATE TABLE students (
   first_name VARCHAR(50) NOT CASESPECIFIC
);

INSERT INTO students(first_name) VALUES ('George');
INSERT INTO students(first_name) VALUES ('   George');

Notice that this sample does not allow inserting values with upper and lower case letters in the NOT CASESPECIFIC column because it takes it as the same value. Because the column does not supervise the case specification, the 'GEORGE' and 'george' values are checked as the same information.

The following rows are taken as duplicated row errors:

INSERT INTO students(first_name) VALUES ('GEORGE');
INSERT INTO students(first_name) VALUES ('GeorGe');
INSERT INTO students(first_name) VALUES ('George  ');
INSERT INTO students(first_name) VALUES ('GeOrge');
INSERT INTO students(first_name) VALUES ('GEorge');
INSERT INTO students(first_name) VALUES ('George');

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

Query

SELECT
   first_name
FROM
   employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY first_name;

Output

first_name
George
John
WIlle
Marco
Mary
GEORGE
GEORGE
GeorgE
JOHN
JOHN

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
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
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 CASESPECIFIC and database mode is TERA Mode

Teradata

Query

SELECT last_name
FROM employees
GROUP BY last_name
HAVING last_name = 'Snow';

Output

last_name
Snow

Snowflake

Query

SELECT last_name
FROM employees
GROUP BY last_name
HAVING RTRIM(last_name) = RTRIM('Snow');

Output

last_name
Snow

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 last_name = 'SNOW' THEN 'GLOBAL COOL SALES'
          ELSE 'Other'
      END AS department_full_name
FROM employees;

Output

first_name	last_name	department_full_name
GEORGE		Other
JOHN	SnoW	Other
Mary		Other
JOHN	snow	Other
WIlle	SNOW	GLOBAL COOL SALES
George	Snow	Other
GeorgE		Other
GEORGE		Other
Marco	SnoW	Information Technology
John	SNOW	Information Technology

Snowflake

Query

SELECT
   first_name,
   last_name,
   CASE
      WHEN UPPER(RTRIM(department)) = UPPER(RTRIM('EngineerinG'))
         THEN 'Information Technology'
      WHEN RTRIM(last_name) = RTRIM('SNOW')
         THEN 'GLOBAL COOL SALES'
      ELSE 'Other'
   END AS department_full_name
FROM
   employees;

Output

first_name	last_name	department_full_name
GEORGE		Other
JOHN	SnoW	Other
Mary		Other
JOHN	snow	Other
WIlle	SNOW	GLOBAL COOL SALES
George	Snow	Other
GeorgE		Other
GEORGE		Other
Marco	SnoW	Information Technology
John	SNOW	Information Technology

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 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 UPPER(RTRIM(e.department)) = UPPER(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

Known Issues

1. there are some mode-specific SQL statement restrictions: BEGIN TRANSACTION, END TRANSACTION, COMMIT [WORK].

2. Data insertion may differ in Snowflake since the case specification is not bound to the column declaration.

3. GROUP BY may differ in order, but group the correct values.

4. ORDER BY behaves differently in Snowflake.

5. If a function has a TRIM() from the source code, this workaround will add the required functions to the source code. So, RTRIM will be applied to the TRIM() source function.

Related EWIs

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