Second parameter added to SQL's LTRIM and RTRIM @ RPGPGM.COM

Tuesday, May 2, 2017

Second parameter added to SQL's LTRIM and RTRIM

2nd parameter added to sql trim built in functions rtrim and ltrim

As part of the goodies that came with the recent Technical Refreshes, TR, was the addition of a second parameter to SQL's Left trim, LTRIM, and Right trim, RTRIM, built in functions. The second parameter has the same functionality as it does in CL and RPG's trim built in functions, when used trimming particular character from either the left or right of the string.

The syntax of these SQL functions is remarkably similar to that of the CL built in function:

  LTRIM(<string>,<characters to trim>)

  RTRIM(<string>,<characters to trim>)

The string can either be that, a string of characters, or a variable. Basically the functionality of the two functions is the same, just differs from which end of the string it does its work.

Let me jump straight to examples to show how these functions work.

Prior to these TR the functions would just remove blanks from the string:

01  exec sql SET :String = LTRIM('   Text') ;
02  dsply ('LTRIM 1 String = <' + String + '>') ;

03  exec sql SET :String = LTRIM('xxxText') ;
04  dsply ('LTRIM 2 String = <' + String + '>') ;

When this RPG code is executed I would see:

DSPLY  LTRIM 1 String = <Text               >
DSPLY  LTRIM 2 String = <xxxText            >

The first statement, line 1, does trim the spaces from the front of the string. But the second, line 3, does not remove the 'x'.

With the addition of the second parameter I can add it to the statement from line 2 and remove all of the 'x' from the front of the string.

05  exec sql SET :String = LTRIM('xxxText','x') ;
06  dsply ('LTRIM 3 String = <' + String + '>') ;


DSPLY  LTRIM 3 String = <Text               >

One thing to be careful of is that the second parameter is case sensitive. If I do the same SQL statement with an upper case 'X' the trim does not happen.

07  exec sql SET :String = LTRIM('xxxText','X') ;
08  dsply ('LTRIM 4 String = <' + String + '>') ;

DSPLY  LTRIM 4 String = <xxxText            >

When using the second parameter if I do not give blank it will not be trimmed, as the example below shows.

09  exec sql SET :String = LTRIM('x xText','x') ;
10  dsply ('LTRIM 5 String = <' + String + '>') ;


DSPLY  LTRIM 5 String = < xText             >

If I want to trim multiple characters I can give them as a list in the second parameter, and they do not have to be in the order they occur in the string.

11  exec sql SET :String = LTRIM('xyzText','xyz') ;
12  dsply ('LTRIM 6 String = <' + String + '>') ;

13  exec sql SET :String = LTRIM('xyzText','zyx') ;
14  dsply ('LTRIM 7 String = <' + String + '>') ;  


DSPLY  LTRIM 6 String = <Text               >
DSPLY  LTRIM 7 String = <Text               >

The RTRIM function does the same, but from the right side of the string.

17  exec sql SET :String = RTRIM('Textxxx','x') ;
18  dsply ('RTRIM 2 String = <' + String + '>') ;

19  exec sql SET :String = RTRIM('Textxyz ','xyz') ;
20  dsply ('RTRIM 3 String = <' + String + '>') ;


DSPLY  RTRIM 1 String = <Text               >
DSPLY  RTRIM 2 String = <Text               >

Rather than use string I can use variables to replace either of the parameters. When I do use a variable I must prefix it with a colon ( : ) so that SQL know that it is variable name.

21  dcl-s String char(20) inz('abc Text') ;
22  dcl-s TrimChars char(10) inz('abc') ;

23  exec sql SET :String = LTRIM(:String,:TrimChars) ;
24  dsply ('String = <' + String + '>') ;


DSPLY  String = <Text                >

As the variable TrimChar, line 22, is longer than the value I initialized it with it will be packed with blanks. Therefore, when I use for the characters to be trimmed it will also remove blanks.

If I wanted to trim multiple strings or variables at once I could use a SQL Select statement using the default "dummy" table:

25  exec sql SELECT LTRIM('xxxTextxxx','x'),
                    RTRIM('xxxTextxxx','x')
               INTO :String,:String1
               FROM SYSIBM.SYSDUMMY1 ;

26  dsply ('String  = <' + String + '>') ;
27  dsply ('String1 = <' + String1 + '>') ;


DSPLY  String  = <Textxxx            >
DSPLY  String1 = <xxxText            >

I could use both the LTRIM and RTRIM to remove leading and trailing characters, but the TRIM function will do the same for me with just one built in function.

28  exec sql SET :String = RTRIM(LTRIM('xxxTextxx','x'),'x') ;
29  dsply ('1 String = <' + String + '>') ;

30  exec sql SET :String = TRIM(BOTH 'x' FROM 'xxxTextxx') ;
31  dsply ('2 String = <' + String + '>') ;


DSPLY  1 String = <Text               >
DSPLY  2 String = <Text               >

You can learn more about this from the IBM website:

This article was written for IBM i 7.3 TR2, and will work for 7.2 TR6 too.