TRANSLATE

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-TRANSLATE---------------------------------------------------->
 
>--(--expression--+----------------------------------------------+--)-><
                  '-,--to-string--+----------------------------+-'
                                  '-,--from-string--+--------+-'
                                                    '-,--pad-'
 

The TRANSLATE function returns a value in which one or more characters in expression may have been converted into other characters.

expression
An expression that specifies the string to be converted expression must be any built-in numeric or string data type. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR.
to-string
A string that specifies the characters to which certain characters in expression are to be converted. This string is sometimes called the output translation table. The string must be any built-in numeric or string constant. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR. A character string argument must have an actual length that is not greater than 256.

If the length attribute of the to-string is less than the length attribute of the from-string, then the to-string is padded to the longer length using either the pad character if it is specified or a blank if a pad character is not specified. If the length attribute of the to-string is greater than the length attribute of the from-string, the extra characters in to-string are ignored without warning.

from-string
A string that specifies the characters that if found in expression are to be converted. This string is sometimes called the input translation table. When a character in from-string is found, the character in expression is converted to the character in to-string that is in the corresponding position of the character in from-string.

The string must be any built-in numeric or string constant. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR. A character string argument must have an actual length that is not greater than 256.

If there are duplicate characters in from-string, the first one scanning from the left is used and no warning is issued. The default value for from-string is a string starting with the character X'00' and ending with the character X'FF' (decimal 255).

pad
A string that specifies the character with which to pad to-string if its length is less than from-string. The string must be a character string constant with a length of 1. The default is an SBCS blank.

If the first argument is a Unicode graphic or UTF-8 string, no other arguments may be specified.

If only the first argument is specified, the SBCS characters of the argument are converted to uppercase, based on the CCSID of the argument. Only SBCS characters are converted. The characters a-z are converted to A-Z, and characters with diacritical marks are converted to their uppercase equivalent, if any. If the first argument is UTF-16, UCS-2, or UTF-8, the alphabetic UTF-16, UCS-2, or UTF-8 characters are converted to uppercase. Refer to the UCS-2 level 1 mapping tables section of the Globalization topic in the iSeries Information Center for a description of the monocasing tables that are used for this conversion.

If more than one argument is specified, the result string is built character by character from expression, converting characters in from-string to the corresponding character in to-string. For each character in expression, the same character is searched for in from-string. If the character is found to be the nth character in from-string, the resulting string will contain the nth character from to-string. If to-string is less than n characters long, the resulting string will contain the pad character. If the character is not found in from-string, it is moved to the result string unconverted.

Conversion is done on a byte basis and, if used improperly, may result in an invalid mixed string. The SRTSEQ attribute does not apply to the TRANSLATE function.

The result of the function has the same data type, length attribute, actual length, and CCSID as the argument. If the first argument can be null, the result can be null. If the argument is null, the result is the null value.

When TRANSLATE is specified in a query, the query cannot contain:

Examples