DB2 automatic trim tailing whitespace

Question

This is data in TB_USER table

USER_USERID    
 --------------     
 A111           
 A9999          
 ADMIN          
 AHO            
 AHO2           
 AHO3           
 AHO4     

...and schema of TB_USER table is

 COLUMN_NAME           DATA_TYPE  DATA_LENGTH  
 --------------------  ---------  ----------- 
 USER_USERID           VARCHAR    15        

When I execute following SELECT statement:

 SELECT USER_USERID FROM TB_USER WHERE USER_USERID = 'ADMIN '

...and:

 SELECT USER_USERID FROM TB_USER WHERE USER_USERID = 'ADMIN'

DB2 retreive same output following

USER_USERID    
 -------------- 
 ADMIN   

How to fix this problem?

Answer 1

This behaviour is as designed - equality predicates for string comparisons won't consider trailing spaces. As Rakesh suggested, the LIKE predicate will consider the trailing spaces. It depends upon your use case which you should go with.

Relevant documentation:

DB2 9.7 Infocenter - Assignments and comparisons

"When comparing character strings of unequal lengths, the comparison is made using a logical copy of the shorter string, which is padded on the right with blanks sufficient to extend its length to that of the longer string. This logical extension is done for all character strings, including those tagged as FOR BIT DATA."

DB2 9.7 Infocenter - LIKE predicate

"The LIKE predicate searches for strings that have a certain pattern. The pattern is specified by a string in which the underscore and the percent sign may have special meanings. Trailing blanks in a pattern are part of the pattern."

Answer 2

You should use the RTRIM or TRIM function.

SELECT USER_USERID FROM TB_USER WHERE RTRIM(USER_USERID) = 'ADMIN'