TRANSLATE

The schema is SYSIBM.

string-expression

An expression that specifies the string to be converted. string-expression must return a value that is a built-in character or graphic string data type that is not a LOB. If string-expression is an EBCDIC or ASCII graphic string and string-expression is the only argument that is specified, the locale name that is specified by the CURRENT LOCALE LC_CTYPE special register must be a non-blank string.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

to-string

An expression that specifies the characters to which certain characters in string-expression are to be converted. This string is sometimes called the output translation table. to-string must return a value that is a built-in character or graphic string data type that is not a LOB.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

If the length of to-string is less than the length of from-string, to-string is padded to the length of from-string with the pad or a blank. If the length of to-string is greater than from-string, the extra characters in to-string are ignored without warning.

from-string

An expression that specifies the characters that if found in string-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 string-expression is converted to the character in to-string that is in the corresponding position of the character in from-string.

from-string must return a value that is a built-in character or graphic string data type that is not a LOB.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

If from-string contains duplicate characters, the first occurrence of the character is used, and no warning is issued. The default value for from-string is a string that starts with the character X'00' and ends with the character X'FF' (decimal 255).

pad

An expression that specifies the character with which to pad to-string if its length is less than from-string. pad is an expression that must return a value that is a built-in character or graphic string data type that is not a LOB and has a length of 1. A length of 1 is one single byte for character strings and one double byte string for graphic strings. The default is a blank that is appropriate for string-expression.

If string-expression is the only argument that is specified, the string is converted to uppercase based on the locale name that is specified by the CURRENT LOCALE LC_CTYPE special register, as follows:

• blank — SBCS lowercase characters a-z are converted to SBCS uppercase characters A-Z, and characters with diacritical marks are not converted. If the string contains MIXED or DBCS characters, full-width lowercase characters a-z are converted to full-width Latin uppercase characters A-Z. For optimal performance, specify a blank string unless your data must be processed by using rules that are defined by a specific locale.
• UNI — The conversion uses both the NORMAL and SPECIAL casing capabilities as described in Select the conversions. You must not specify UNI when string-expression is EBCDIC data.
• locale name — The locale defines the rules for conversion to uppercase characters.

For Unicode data, usage of the TRANSLATE function (the TRANSLATE function with one argument is equivalent to the UPPER function) can result in expansion if certain characters are processed. You should ensure that the result string is large enough to contain the result of the expression.

If more than one argument is specified, the result string is built character-by-character from string-expression with each character in from-string being converted to the corresponding character in to-string. For each character in string-expression, the from-string is searched for the same character. 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. If the character is not found in from-string, it is moved to the result string without being converted.

The string can contain mixed data. If only one argument is specified, the UPPER function is performed on the argument, and the rules for operating on mixed data in the UPPER function are observed. Full-width Latin lowercase a-z are converted to full-width Latin uppercase letters A-Z. Otherwise, the function operates on a strict byte-count basis, and the result is not necessarily a properly formed mixed data character string.

The encoding scheme of the result is the same as string-expression. The data type of the result of the function depends on the data type of string-expression, to-string, from-string, and pad:

• VARCHAR if string-expression is a character string. The CCSID of the result depends on the arguments:
  • If string-expression, to-string, from-string, or pad is bit data, the result is bit data.
  • If string-expression, to-string, from-string, and pad are all SBCS:
    • If string-expression, to-string, from-string, and pad are all SBCS Unicode data, the CCSID of the result is the CCSID for SBCS Unicode data.
    • If string-expression is SBCS Unicode data, and to-string, from-string, or pad are not SBCS Unicode data, the CCSID of the result is the mixed CCSID for Unicode data.
    • Otherwise, the CCSID of the result is the same as the CCSID of string-expression.
  • Otherwise, the CCSID of the result is the mixed CCSID that corresponds to the CCSID of string-expression. However, if the input is EBCDIC or ASCII and there is no corresponding system CCSID for mixed, the CCSID of the result is the CCSID of string-expression.
• VARGRAPHIC if string-expression is a graphic. The CCSID of the result is the same as the CCSID of source-string.

The result can be null; if the first argument is null, the result is the null value.

   SELECT TRANSLATE ('abcdef')
     FROM SYSIBM.SYSDUMMY1

   SELECT TRANSLATE (:SITE)
     FROM SYSIBM.SYSDUMMY1

   SELECT TRANSLATE (:SITE, 'j', 'B')
     FROM SYSIBM.SYSDUMMY1

   SELECT TRANSLATE (:SITE, 'ei', 'aa')
     FROM SYSIBM.SYSDUMMY1

   SELECT TRANSLATE (:SITE, 'bA', 'Bay', '%')
     FROM SYSIBM.SYSDUMMY1

   SELECT TRANSLATE (:SITE, 'r', 'Bu')
     FROM SYSIBM.SYSDUMMY1

   SELECT TRANSLATE (:SITE, '$$', 'Ll')
     FROM SYSIBM.SYSDUMMY1

   SELECT TRANSLATE (:SITE, 'pLA', 'Place', '.')
     FROM SYSIBM.SYSDUMMY1