DB2 automatic trim tailing whitespace
db2, sql
Solution
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."
Problem
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?