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:
Part of the Teradata SQL Tuning and Query Rewriting guide.
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.
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.
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
%t% for CHAR
Newbie question,
Wouldn’t the padding be a left padding, instead of right padding?
Yes
Using trim function in the where clause? Though it will increase the cost.