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!
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.