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.

Categories: Notes

Leave a Reply

Your email address will not be published. Required fields are marked *