Teradata LIKE – How to avoid unexpected Results!

Have you ever noticed that the Teradata LIKE operator behaves differently depending on whether you apply it to a column of data type CHAR or VARCHAR? Let us take the table below as an example:

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


We're inserting exactly one row for our demonstration:

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

First, we run the following SQL statement for the column of datatype VARCHAR:

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

Next, we execute the same query on the column of data type CHAR:
SELECT TheChar FROM TheLikeTest WHERE TheChar LIKE ‘%t';
-> Result: No rows returned

As we see, the second query does not return a result. How is that possible?

The solution to this puzzle is simple: Since it is a column of the data type CHAR, space characters are padded to 20 characters. Our query expects a ‘t' at the end of the column content, but there is certainly a space character there!

How can this undesired behaviour be avoided? Leave a comment with your solution!