Spaces in SQL are only used in character strings and as you may know trailing spaces or blanks are ignored when DB2 compares two character strings. Sometimes this fact leads to the wrong conclusion that DB2 strips blanks in VARCHAR columns before they are inserted or updated, but DB2 does not strip spaces in this case. All these facts may seem straightforword and wellproven, but trailing spaces are not always that predictable.

How do you compare two character strings that you want to be exactly the same in SQL not only in contens but also when it comes to the number of trailing blanks. LIKE offers this functionality. The predicate SPACE(5) LIKE SPACE(4) is false, try it! The sad thing about LIKE is that the expression after LIKE cannot be a column (maybe in DB2 version 8 New Function Mode) and what if the expression after LIKE contains % or _, then suddenly the comparison does not quite work as you expect.

For some years it has been popular to use larger data structures so nowadays VARCHAR columns longer than 254 characters has become regular in our DB2 tables. Also LOB datatypes has been added to DB2 and they are much longer that 254 bytes. The "funny" thing is that when you compare somthing longer than 255 bytes with something shorter then you will receive a SQLERROR -134. DB2 simply cannot compare character string longer that 255 bytes. Fortunately LIKE can handle up to 4095 bytes with the limitations mentioned earlier.

Often you want to know if a column contains only spaces. For columns with a character length of 254 or less you do this with a COLUMN = '', but how do you manage this simple task if a column is larger than 254 characters. In this case you can use the comparison RTRIM(COLUMN) LIKE '' which produces the correct result. Beware that COLUMN LIKE '' does not work for VARCHAR columns containing anything else than the empty string.