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

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 behavior be avoided? Leave a comment with your solution!

Buy now at Amazon
  • Newbie question,

    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.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like