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.
Yes; SQL is offers a huge set of functions and we can tune into them in RPG using embedded SQL. I wish that RPG had %upper and %lower but it doesn't so one turns to %xlate. Less effort than using a file/table is to use SQL's SET, familiar from its UPDATE, like this:
ReplyDeleteExec SQL
Set :UpperVariable = UPPER(:LowerVariable);
It works very nicely -- but I pondered the performance-cost. In the primitive test shown below (no free-form declarations available to me), that does 100000 successive uses of each, I found that using SET and UPPER was about 100 times slower than using %late for the same task. Moral: if the conversion is needed just a few times, SET is fine but if you are using it for each record of some large file you are reading, it will be perhaps prohibitively slow.
H Option(*NoDebugIO: *SrcStmt)
D LowerVariable S 10 inz('rhubarb')
D UpperVariable S 10
D i S 10I00
D Limit S 10I00 Inz(100000)
D StartTime S Z
D EndTime S Z
D Interval S 10I00
D lower C 'abcdefghijklmnopqrstuvwxyz'
D Upper C 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
/Free
//===================================================================
// RPG's %Xlate
//===================================================================
StartTime=%Timestamp();
For i=1 to Limit;
UpperVariable=%Xlate(lower: Upper: LowerVariable);
endfor;
EndTime=%Timestamp();
Interval=%Diff(EndTime:StartTime: *MSECONDS);
Dsply (%char(Interval)+' microsec %Xlate') '*EXT';
//===================================================================
// SQL's UPPER
//===================================================================
StartTime=%Timestamp();
For i=1 to Limit;
Exec SQL
Set :UpperVariable = UPPER(:LowerVariable);
endfor;
EndTime=%Timestamp();
Interval=%Diff(EndTime:StartTime: *MSECONDS);
Dsply (%char(Interval)+' microsec SQL') '*EXT';
//===================================================================
// Terminated return
//===================================================================
*InLR=*On;
Return;
/end-free
You may be able to see the code aligned properly in the timing program above if you view a copy using Courier font.
Here's the result of a run of that pgm.
Job 969491/NRE40411/NIGEL1 started on 02/26/15 at 06:06:43 in subsystem QINT
DSPLY 73000 microsec %Xlate
DSPLY 9157000 microsec SQL
... so you see the SQL took 125 times longer than the %Xlate. Pity!
I thank Ken Danforth at Amscan for pointing out the ability to use SET in an RPG pgm. If timing is not an issue, it opens many possibilities to use SQL's functions.
NVR
Thank you for the test program and results. While SQL is great for making so many things easier than coding them in RPG, it does come with a performance hit.
DeleteI hope that over time IBM will be be able to work on tuning SQL and make the difference between it and RPG negligible.
As with any performance test, you need to be careful not to make broad generalizations based on the result of one test. This claim was made: "if you are using it for each record of some large file you are reading, it will be perhaps prohibitively slow" despite the fact that this tests does not include reading records from a file.
ReplyDeleteIf data was actually read from a file, then the SQL UPPER function would be done on the cursor declaration so that the UPPER processing is performed as part of the SQL Fetch operation. Instead of a separate SQL SET operation like this test uses. I believe that the non-SQL version would require the character column to be read into a local variable and then another RPG operation to execute the %XLATE function. I believe the performance of this more realistic test scenario would be much closer, especially if the SQL best practice of blocked FETCH requests is used.