I have to admit that, when trimming strings with SQL, I tend to just use the TRIM function to strip off all leading and trailing spaces. But the LTRIM and RTRIM functions really do deserve more attention because they have been recently enhanced to support trim expressions. That is, all of the characters you might want to trim.
Here’s an example. Imagine you have a text representation of a value and, for some reason, some of these values have Pound, Dollar or Euro signs at either the start or the end of the string. Here’s our table:
CURRENCYVALUE $400 £250 300€
So to trim the left hand currency symbols, all we need to do is this:
select ltrim(CURRENCYVALUE, '$£') from QTEMP.TEST
And to remove that right hand Euro symbol (along with any trailing spaces), we can do this:
select rtrim(CURRENCYVALUE, '€ ') from QTEMP.TEST
Of course, this only becomes really useful when we put it all together so that this:
select rtrim(ltrim(CURRENCYVALUE, '$£'), '€ ') as NUMBERS from QTEMP.TEST
… returns this:
NUMBERS 400 250 300
Admittedly this example is a bit of an artificial one, but being able to easily strip multiple characters from the beginning and end of a string does simplify the process of cleaning up data received from external sources.