Guru: LTRIM + RTRIM > TRIM

April 2, 2018 Ted Holt

When IBM enhances SQL for my favorite database management system, I feel so happy I could dance like Karlos Klaumannsmoller selling diabetes medicine. It is my pleasure today to let you know (in case you don’t already know) that IBM has once again enhanced two SQL functions to make them as powerful as their RPG counterparts.

The functions to which I refer are LTRIM (left trim) and RTRIM (right trim). Until recently these functions could only remove blanks (or hexadecimal zeros, for some data types) from the beginning (left) or end (right) of a string. IBM recently added to these functions a second parameter, with which you can specify a set of characters to be removed from the string. The only catch is that you must run IBM i 7.2 or higher.

With this enhancement, LTRIM and RTRIM become more powerful than TRIM, which can remove a single character from a string value. For instance, suppose you wish to remove a dollar sign and leading blanks before an amount of money. TRIM cannot do the job, but LTRIM can.

select c.comment,
       ltrim(comment,' $') as trimmed
  from session.comments as c

TRIM has one advantage over LTRIM and RTRIM — it can remove a single character from both ends of the string. LTRIM and RTRIM operate on only one end of the string. However, you can easily circumvent this limitation by nesting LTRIM and RTRIM inside one another.

select c.comment,
       rtrim(ltrim(comment,' $')) as trimmed
  from session.comments as c

A feature you may have a use for is to specify the list of characters in something other than a literal. In this example, I specify a list of characters in a global variable called NonNumeric.

create or replace variable MyLib.NonNumeric varchar(256) 
   default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz $.'

select c.comment,
       rtrim(ltrim(comment,NonNumeric),NonNumeric)
          as trimmed
  from session.comments as c

TRIM, LTRIM and RTRIM can’t ignore embedded characters, but the REPLACE function can. Let’s get rid of commas, which serve as thousands separators here in the United States of America.

select c.comment, 
       replace(rtrim(ltrim(comment,' $')),',','')
          as trimmed
  from session.comments as c

You might also find regular expressions helpful for retrieving the data you desire from unstructured strings. In this example, I use the REGEXP_SUBSTR (AKA REGEXP_EXTRACT) function to dig out an embedded number.

select c.comment, 
       dec(regexp_substr(comment, '\d*\.*\d+'),7,2)
          as trimmed
  from session.comments as c

The expression looks for zero or more digits (\d*) followed by zero or more periods (\.*) followed by one or more digits (\d+). If you use regular expressions, it may take you a while to produce exactly the right expression for your needs. Regular expressions are almost as cryptic as tax laws.

Mix and match these functions to get the results you need. There are a lot of ways to remove unwanted characters from strings.

Thus is proven yet again the ancient dictum: where there’s a will, there’s a function. Or something like that.

RELATED STORIES

Enhanced LTRIM and RTRIM built-in functions

Quick-Start: Regex Cheat Sheet