SQL iQuery Script Built-in Function



Convert Characters

The XLATE() built-in function returns the data from parameter 3 after converting each character in parameter 1 detected in parameter 3, to the corresponding character in parameter 2.
This built-in function works similar to the RPG IV %XLATE built-in function.


The XLATE() built-in function scans parameter 3 for the first character in parameter 1. If found, it replaces that character with the corresponding value in parameter 2. Then it moves to the 2nd character in parameter 1 and repeats the process until all characters from parameter 1 have been processed.


  1. One or more characters that are to be replaced when found in parameter 3.
  2. One character for each of the characters in parameter 1, or an empty set (two consecutive quotes) to indicate the characters are removed.
  3. A session variable that is searched for the characters in parameter 1.
eval &Amt = '$12,275,451.20';
eval &Due = xlate('%,','',&amt);
eval &objName = 'CUST*';
eval &genObj = xlate('*','%', &objName);
#msg Amount Due is &DUE #msg SQL-ready generic object is &genObj

The output from the above script is:

Amount Due is 12275451.20 
SQL-ready generic object is CUST%