Have you observed the divergent 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.
SELECT TheVarchar FROM TheLikeTest WHERE TheVarchar LIKE '%t'; -> Result: 'Anytext' (1 Row)
Next, we will execute the identical 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.
%t% for CHAR
Wouldn’t the padding be a left padding, instead of right padding?
Using trim function in the where clause? Though it will increase the cost.