Tuesday, April 29, 2014

Trimming the fun? LTRIM and RTRIM extended

One of the newer features in DB2 is extended trimming capability in the functions LTRIM and RTRIM. Since Fixpack 2 of DB2 10.1 both functions have an optional second parameter which is used to specify the characters to be removed from the beginning or end of a string. Before it was only possible to remove blanks. If the second parameter is omitted, blanks are removed by default.

To trim your calories you could do the following:

db2 => values rtrim('All I eat: marzipan, vegetables, fruits',' ,abefgilrstuv')

All I eat: marzipan                   

  1 record(s) selected.

The enhanced LTRIM and RTRIM functions can be used together with other functions of course:
db2 => values replace(ltrim('jogging and eating are great',' adgijno'),'are','is')

eating is great            

  1 record(s) selected.

The examples are just some food for thought about what is possible.