TRANSLATE scalar function

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.

UNI_90

The conversion uses Unicode Standard 9.0.0 and the NORMAL casing capability, as described in Select the conversions. You must not specify UNI_60 when string-expression is EBCDIC data.

UNI_90

The conversion uses Unicode Standard 6.0.0 and the NORMAL casing capability, as described in Select the conversions. You must not specify UNI_90 when string-expression is EBCDIC data.

UNI_SIMPLE

Case conversions use the NORMAL casing capabilities as described in Select the conversions. UNI_SIMPLE cannot be used with 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 one piece at a time from string-expression. If string-expression is CHAR or VARCHAR, a piece is one byte long. If string-expression is GRAPHIC or VARGRAPHIC, a piece is two bytes long.

For each piece in string-expression, the from-string is searched for that same piece.

• If the piece in string-expression matches a piece in from-string, and there is a corresponding piece in to-string, that piece in to-string is moved to the result string. If the piece in string-expression matches a piece in from-string, but there is no corresponding piece in to-string, the pad is moved to the result string.
• If the piece in string-expression does not match any piece in from-string, the piece in string-expression is moved to the result string.

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. If more than one argument is specified, the function operates as previously described for CHAR and VARCHAR data. 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.