SSC-EWI-TD0031

The result may differ due to char type having a fixed length in Teradata

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

Low

Description

Since Teradata CHAR data type has a fixed length, some functions such as LIKE will try to match the complete column instead of the word inserted into the column, resulting in false matches. However, Snowflake the CHAR type is of variable size, meaning that the LIKE functions will always try to match against the inserted values. Take the following code as an example:

Example Code

Input:

IN -> Teradata_01.sql

CREATE TABLE table1
(
    col1 VARCHAR(36),
    col2 CHAR(36)
);

INSERT INTO table1 VALUES ('Gabriel', 'Gabriel');
INSERT INTO table1 VALUES ('Barnum', 'Barnum');
INSERT INTO table1 VALUES ('Sergio', 'Sergio');

SELECT col1 FROM table1 where col1 LIKE 'Barnum';
-- The result is a single row with 'Barnum'
SELECT col2 FROM table1 where col2 LIKE 'Barnum';
-- It does not return any row

Output:

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE table1
(
    col1 VARCHAR(36),
    col2 CHAR(36)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
;

INSERT INTO table1
VALUES ('Gabriel', 'Gabriel');

INSERT INTO table1
VALUES ('Barnum', 'Barnum');

INSERT INTO table1
VALUES ('Sergio', 'Sergio');

SELECT
    col1 FROM
    table1
where col1 ILIKE 'Barnum';
-- The result is a single row with 'Barnum'
    SELECT
    col2 FROM
    table1
    where
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0031 - THE RESULT OF LIKE MAY DIFFER DUE TO CHAR TYPE HAVING A FIXED LENGTH IN TERADATA ***/!!! col2 ILIKE 'Barnum';
    -- It does not return any row

Recommendations

• If you need more support, you can email us at [email protected]