TERA Mode For Strings Comparison

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

Description

Teradata modes description

The Teradata database has different modes for running queries: ANSI Mode (rules based on the ANSI SQL: 2011 specifications) and TERA mode (rules defined by Teradata). Please review the following Teradata documentation for more information.

Teradata mode for string

For strings, the Teradata mode works differently. As it is explained in the following table based on the Teradata documentation:

Feature	ANSI mode	Teradata mode
Default attribute for character comparisons	CASESPECIFIC	NOT CASESPECIFIC
Default TRIM behavior	TRIM(BOTH FROM)	TRIM(BOTH FROM)

Translation specification summary

For this feature, the intended translation is focused only on Tera Mode. Notice that ANSI mode is added here just for comparison.

Mode	Column constraint values	Teradata behavior	SnowConvert expected behavior
ANSI Mode	CASESPECIFIC	CASESPECIFIC	No constraint added.
	NOT CASESPECIFIC	CASESPECIFIC	Add COLLATE 'en-cs' in column definition.
Teradata Mode	CASESPECIFIC	CASESPECIFIC	In most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM( expression )
	NOT CASESPECIFIC	NOT CASESPECIFIC	In most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM(UPPER( expression ))

Sample Source Patterns

Setup data

Teradata

IN -> Teradata_01.sql

CREATE TABLE databaseTest.employees (
   first_name VARCHAR(50) NOT CASESPECIFIC,
   last_name VARCHAR(50) CASESPECIFIC,
   department VARCHAR(50) CASESPECIFIC
);

INSERT INTO databaseTest.employees(first_name, last_name, department) VALUES ('George', 'Snow', 'Sales');
INSERT INTO databaseTest.employees(first_name, last_name, department) VALUES ('Jonh', 'Snow', 'Engineering');

Snowflake

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE databaseTest.employees (
   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": "07/26/2024" }}'
;

INSERT INTO databaseTest.employees (first_name, last_name, department)
VALUES ('George', 'Snow', 'Sales');

INSERT INTO databaseTest.employees (first_name, last_name, department)
VALUES ('Jonh', 'Snow', 'Engineering');

Evaluation operation

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

This example demonstrates the usage of a column set up as NOT CASE SPECIFIC 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

IN -> Teradata_02.sql

SELECT *
FROM databaseTest.employees
WHERE first_name = 'GEorge ';

Output

first_name	last_name	department
George	Snow	Sales

Snowflake

Query

OUT -> Teradata_02.sql

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

Output

first_name	last_name	department
George	Snow	Sales

Sample 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

IN -> Teradata_03.sql

SELECT *
FROM databaseTest.employees
WHERE last_name = 'SNOW ';

Output

No results.

Snowflake

Query

OUT -> Teradata_03.sql

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

Output

No results.

LIKE operation

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

Sample 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