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

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 ANSI Mode

Teradata

Query

SELECT *
FROM employees
WHERE first_name = 'George      ';

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

Case 2: Column constraint is CASESPECIFIC and database mode is ANSI 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 ANSI Mode

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

Teradata

Query

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
6	GEORGE		sales

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
7	GEORGE		salEs
6	GEORGE		sales

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

Teradata

Query

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

Output

employee_id	first_name	last_name	department
4	Marco	SnoW	EngineerinG

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
4	Marco	SnoW	EngineerinG

LIKE operation

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

Teradata

Query

SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

Snowflake

Query

SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

Case 2: Column constraint is CASESPECIFIC and database mode is ANSI 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 last_name LIKE 'Snow';

Output

employee_id	first_name	last_name	department
1	George	Snow	Sales

Case 3: CAST NOT CASESPECIFIC column to NOT CASESPECIFIC and database mode is ANSI 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 CASESPECIFIC column to NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

Query

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

Output

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

Snowflake

Query

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

Output

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

IN Operation

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

Teradata

Query

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

Output

employee_id	first_name	last_name	department
6	GEORGE		sales
7	GEORGE		salEs

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
6	GEORGE		sales
7	GEORGE		salEs

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

Teradata

Query

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

Output

employee_id	first_name	last_name	department
5	Mary		SaleS

Snowflake

Query

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

Output

employee_id	first_name	last_name	department
5	Mary		SaleS

ORDER BY clause

Notice that this functional equivalence can differ.

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

Teradata

Query

SELECT department_name
FROM departments
ORDER BY department_name;

Output

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

Snowflake

Please review FDM. The order differs in the order of insertion of data.

Query

SELECT
   department_name
FROM
   departments
ORDER BY
   UPPER(department_name);

Output

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

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

Teradata

Query

SELECT last_name
FROM employees
ORDER BY last_name;

Output

department
Finance
Human Resources
Information Technology
Sales

Snowflake

Query

SELECT last_name
FROM employees
ORDER BY last_name;

Output

department
Finance
Human Resources
Information Technology
Sales

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

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 ANSI 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 NOT CASESPECIFIC and database mode is ANSI Mode

Teradata

Query

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

Output

GEORGE   

Snowflake

Query

SELECT
   first_name
FROM
   employees
GROUP BY first_name
HAVING
   RTRIM(first_name) = RTRIM('GEORGE');

Output

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

IN -> Teradata_10.sql

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

Output

first_name	last_name	department_full_name
GEORGE		Department Full Name
Mary		GLOBAL SALES
GeorgE		Other
GEORGE		Department Full Name

Snowflake

Query

OUT -> Teradata_10.sql

SELECT first_name,
      last_name,
      CASE
          WHEN RTRIM(department) = RTRIM('SaleS  ') THEN 'GLOBAL SALES'
          WHEN RTRIM(first_name) = RTRIM('GEORGE   ') THEN 'Department Full Name'
          ELSE 'Other'
      END AS department_full_name
FROM employees
WHERE RTRIM(last_name) = RTRIM('   ');

Output

first_name	last_name	department_full_name
GEORGE		Department Full Name
Mary		GLOBAL SALES
GeorgE		Other
GEORGE		Department Full Name

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

Query

IN -> Teradata_14.sql

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
10	JOHN	snow	Finance

Snowflake

Query

OUT -> Teradata_14.sql

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
10	JOHN	snow	Finance

Related EWIs

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