TRANSLATE
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 about 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
about 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 actual length of the to-string is less than the actual length 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 actual length of the to-string is greater than the actual length 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 about 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 topic of the Globalization topic collection 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.
Examples
- Monocase the string 'abcdef'.
Returns the value 'ABCDEF'.SELECT TRANSLATE('abcdef') FROM SYSIBM.SYSDUMMY1
- Monocase the mixed character string.
SELECT TRANSLATE()
FROM SYSIBM.SYSDUMMY1
Returns the value
- Given that the host variable SITE is a varying-length character
string with a value of 'Pivabiska Lake Place'.
Returns the value 'Pivabiska $ake Place'.SELECT TRANSLATE(:SITE, '$', 'L') FROM SYSIBM.SYSDUMMY1
Returns the value 'Pivabiska $ake P$ace'.SELECT TRANSLATE(:SITE, '$$', 'Ll') FROM SYSIBM.SYSDUMMY1
Returns the value 'pivAbiskA LAk. pLA..'.SELECT TRANSLATE(:SITE, 'pLA', 'Place', '.') FROM SYSIBM.SYSDUMMY1