DWHPro Insights

Why Teradata’s LIKE operator behaves differently for CHAR and VARCHAR columns

By Roland Wenzlofsky ·

Why Teradata’s LIKE operator behaves differently for CHAR and VARCHAR columns

Have you observed the different behavior of the Teradata LIKE operator when applied to a CHAR or VARCHAR data type column? Consider the following table as an illustration:

Creating a table:

CREATE TABLE TheLikeTest (
    TheVarchar VARCHAR(20),
    TheChar CHAR(20)
);

To illustrate, we will add a single row.

INSERT INTO TheLikeTest VALUES ('Anytext','Anytext');

We will run the SQL statement for the column with the VARCHAR data type.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

SELECT TheVarchar FROM TheLikeTest WHERE TheVarchar LIKE '%t';
-> Result: 'Anytext' (1 Row)

Next, we will execute the same query on the column with a CHAR data type.


SELECT TheChar FROM TheLikeTest WHERE TheChar LIKE '%t';
-> Result: No rows

Anytext has seven characters, so CHAR(20) pads it with 13 spaces. The VARCHAR suffix matched one fixture row, the padded CHAR suffix matched none, and RTRIM(CHAR-column) matched one; trimming may affect access paths and estimates.

Share your solution in the comments to prevent this undesirable behavior.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

4 thoughts on “Why Teradata’s LIKE operator behaves differently for CHAR and VARCHAR columns”

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam