Last week I discussed how to change the case of alphabetic characters using the new %UPPER and %LOWER BIFs, built in functions, added to CL in IBM i 7.2, you can read it here. I mentioned the limitations of using the %XLATE BIF in RPG to accomplish the same. But there is an easier way using embedded SQL in the RPG to emulate the functionality of CL's %UPPER and %LOWER do.
RPG's %XLATE requires that you need to give all of the characters that should be replaced with what you need them to be replaced by. This is useful for so many situations. But when converting case most programs I have seen many that just have the following:
dcl-c UpperCase 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ; dcl-c LowerCase 'abcdefghijklmnopqrstuvwxyz' ; dcl-s String char(25) ; String = 'rpgpgm.com' ; String = %xlate(LowerCase:UpperCase:String) ;
But what about other letters with accents, circumflexes, umlauts, etc.? The above %XLATE will not translate them as they are not listed in the UpperCase constant. I could modify the constants in the above code to include all those additional characters, but SQL provides us with an easier way.
SQL provides four functions that do the conversion for us:
- LOWER or LCASE
- UPPER or UCASE
Each pair perform the same functionality. I prefer to use LOWER and UPPER as I think it is easier for someone else to understand what the function is doing. Here is a simple example of using all of these functions:
01 dcl-s String1 char(20) inz('abcd æçèíñöû') ; 02 dcl-s String2 like(String1) ; 03 exec sql SET :String2 = UPPER(:String1) ; 04 dsply ('1st = ' + String2) ; 05 exec sql SET :String2 = UCASE(:String1) ; 06 dsply ('2nd = ' + String2) ; 07 exec sql SET :String1 = LOWER(:String2) ; 08 dsply ('3rd = ' + String1) ; 09 exec sql SET :String1 = LCASE(:String2) ; 10 dsply ('4th = ' + String1) ;
Which when run would look like:
DSPLY 1st = ABCD ÆÇÈÍÑÖÛ DSPLY 2nd = ABCD ÆÇÈÍÑÖÛ DSPLY 3rd = abcd æçèíñöû DSPLY 4th = abcd æçèíñöû
Or the functions can be used in SQL statements, for example:
exec sql DECLARE C0 CURSOR FOR SELECT LOWER(FLD1) FROM TESTFILE ; exec sql INSERT INTO MYLIB/TESTFILE (FLD1,FLD2) VALUES (:String1,UPPER(:String2)) ;
These examples show how much easier and safer it is to use the SQL UPPER and LOWER functions, rather than using RPG's %XLATE which could miss a special character, for example "Ł", when performing the translation from upper to lower case or vice versa.
You can learn more about these on the IBM website:
This article was written for IBM i 7.2, and should work for earlier releases too.