Troubleshooting the Teradata LIKE Operator

3
879
Teradata LIKE

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!

Our Reader Score
[Total: 13    Average: 3.6/5]
Troubleshooting the Teradata LIKE Operator written by Roland Wenzlofsky on April 30, 2019 average rating 3.6/5 - 13 user ratings

3 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here