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.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Launch access is open. The single paid plan will be EUR 49 per year.

Get the next issue

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

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

If you work with enterprise data platforms, migrations, performance tuning, or AI-driven delivery teams, DWHPro Letters is written for you. Get the next issue by email.

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

The second query yielded no results due to a 20-space addition to the CHAR data type column. The column content contains a space character instead of the expected 't' at the end.

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

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.