Automatic tail alignment DB2 DB2

Question

This is the data in table TB_USER

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

... and table schema TB_USER

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

When I execute the SELECT statement:

 SELECT USER_USERID FROM TB_USER WHERE USER_USERID = 'ADMIN '

... and:

 SELECT USER_USERID FROM TB_USER WHERE USER_USERID = 'ADMIN'

DB2 returns the following result after

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

How to solve this problem?

Answer 1

This behavior is also designed - equality predicates will not consider trailing spaces to compare strings. As Rakesh suggested, the LIKE predicate will consider trailing spaces. It depends on your use case with which you have to go.

Relevant documentation:

DB2 9.7 Infocenter - Purpose and Comparison

" , , , , , FOR BIT DATA."

DB2 9.7 Infocenter - LIKE

" LIKE , . , . ."

Answer 2

RTRIM TRIM .

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

Answer 3

SELECT USER_USERID FROM TB_USER WHERE USER_USERID = 'ADMIN '

SELECT USER_USERID FROM TB_USER WHERE USER_USERID like 'ADMIN '