SQL - Why pad spaces when comparing characters?

Question

Seems like DB2 and Oracle implement character comparison by padding the shorter string with spaces on right. But why?

Would it not be better if the database engine had trimmed the larger character value instead of padding the shorter one with spaces? i.e. if my query is SELECT * from SOME_TABLE where CUSTOMER_NAME = 'Popeye ', the database engine ends up adding spaces to every row in SOME_TABLE, when instead it could have trimmed the value passed here i.e. 'Popeye '

Also, just curious to understand why does Oracle and DB2 only add spaces on right ?

Answer 1

You are confusing the different types of characters and what is happening. When you execute:

SELECT * from SOME_TABLE where CUSTOMER_NAME = 'Popeye '

No spaces are added "to every row in SOME_TABLE". The only question is the width of the results. This width is set by the properties of the columns in SOME_TABLE, not by what appears in the WHERE clause.

If your CUSTOMER_NAME is declared as a char(x) value, then the string must have that length. Say, it is char(10) and you assign 'Popeye' to it. The value has six characters and this must be padded to 10, somehow. The SQL standard is to pad on the right hand side, making the value 'Popeye '.

If you want variable length strings, then use varchar or varchar2.