MSCEWI2031

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

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Since Teradata CHAR data type has a fixed length, some functions such as REGEXP_SIMILAR and 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 REGEXP_LIKE and LIKE functions will always try to match against the inserted values. Take the following code as an example:

Code Example

Input code:

REGEXP_SIMILAR

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

INSERT INTO table1 (col1, col2)
VALUES ('Mike Bird', 'Mike Bird');

SELECT REGEXP_SIMILAR(col1,'(Mike B(i|y)rd)| (Michael B(i|y)rd)') FROM table1;
--Return 1

SELECT REGEXP_SIMILAR(col2,'(Mike B(i|y)rd)| (Michael B(i|y)rd)') FROM table1;
--Return 0

LIKE

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 code:

REGEXP_SIMILAR

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

INSERT INTO table1 (col1, col2) VALUES ('Mike Bird', 'Mike Bird');

SELECT
--** MSC-WARNING - MSCEWI2020 - REGEXP_SIMILAR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
REGEXP_LIKE(col1,'(Mike B(i|y)rd)| (Michael B(i|y)rd)')
FROM table1;
--Return 1
SELECT
--** MSC-WARNING - MSCEWI2020 - REGEXP_SIMILAR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS **
--** MSC-ERROR - MSCEWI2031 - THE RESULT OF REGEXP_SIMILAR MAY DIFFERS DUE TO CHAR TYPE HAVING A FIXED LENGTH IN TERADATA **
REGEXP_LIKE(col2,'(Mike B(i|y)rd)| (Michael B(i|y)rd)')
FROM table1;
--Return 1

LIKE

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

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

SELECT 
col1
FROM PUBLIC.table1 where col1 LIKE 'Barnum' ;
-- The result is a single row with 'Barnum'

SELECT
col2
FROM PUBLIC.table1 where --** MSC-WARNING - MSCEWI2031 - THE RESULT OF LIKE MAY DIFFERS DUE TO CHAR TYPE HAVING A FIXED LENGTH IN TERADATA **
 col2 LIKE 'Barnum' ;
 -- The result is a single row with 'Barnum'

Recommendations

• Analyze the use given to the function results, maybe the Snowflake REGEXP_LIKE function behavior was the one desired from the start and no changes are required.

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