TRANSLATE scalar function

The schema is SYSIBM.

The function converts all the characters in char-string-exp or graphic-string-exp that also occur in from-string-exp to the corresponding characters in to-string-exp or, if no corresponding characters exist, to the pad character specified by pad-char-exp.

char-string-exp or graphic-string-exp

The string that is to be converted. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function.

to-string-exp

A string of characters to which certain characters in char-string-exp are to be converted.

The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function. If a value for to-string-exp is not specified, and the data type is not graphic, all characters in char-string-exp will be in monocase; that is, the characters a-z will be converted to the characters A-Z, and other characters will be converted to their uppercase equivalents, if they exist. For example, in code page 850, é maps to É, but ÿ is not mapped, because code page 850 does not include Ÿ. If the code point length of the result character is not the same as the code point length of the source character, the source character is not converted.

from-string-exp

A string of characters that, if found in char-string-exp, are to be converted to the corresponding character in to-string-exp.

The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function. If from-string-exp contains duplicate characters, the first one found will be used, and the duplicates will be ignored. If to-string-exp is longer than from-string-exp, the surplus characters will be ignored. If to-string-exp is specified, from-string-exp must also be specified.

pad-char-exp

A single character used to pad to-string-exp if to-string-exp is shorter than from-string-exp. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function. The value must have a length attribute of zero or one. If a zero-length string is specified, characters in the from-string-exp with no corresponding character in the to-string-exp are removed from char-string-exp or graphic-string-exp. If a value is not specified a single-byte blank character is assumed.

With graphic-string-exp, only pad-char-exp is optional (if a value is not specified, the double-byte blank character is assumed), and each argument, including the pad character, must be of a graphic data type.

The data type and code page of the result is the same as the data type and code page of the first argument. If the first argument is a host variable, the code page of the result is the database code page. In a non-Unicode database, if any argument is graphic string, then all the arguments must be graphic strings (SQLSTATE 42815). In a Unicode database: if the first argument is a FOR BIT DATA character string, then no other argument can be a graphic string (SQLSTATE 42846); if the first argument is a graphic string, then no other argument can be a FOR BIT DATA character string (SQLSTATE 42846).

The length attribute and string units of the result is the same as that of the first argument. If any argument can be null, the result can be null. If any argument is null, the result is the null value.

If the arguments are of data type CHAR or VARCHAR, the corresponding characters in to-string-exp and from-string-exp must have the same number of bytes (except in the case of a zero-length string). For example, it is not valid to convert a single-byte character to a multi-byte character, or to convert a multi-byte character to a single-byte character. The pad-char-exp argument cannot be the first byte of a valid multi-byte character (SQLSTATE 42815).

The characters are matched using a binary comparison. The database collation is not used.

If only char-string-exp is specified, single-byte characters will be monocased, and multi-byte characters will remain unchanged.