>>-TRANSLATE--(--char-string-exp--------------------------------> >--+-----------------------------------------------------------+--> | .-,--' '----------. | '-,--to-string-exp--,--from-string-exp--+-----------------+-' '-,--pad-char-exp-' >--)-----------------------------------------------------------><
>>-TRANSLATE--(--graphic-string-exp--,--to-string-exp--,--from-string-exp--> .-,--' '----------. >--+-----------------+--)-------------------------------------->< '-,--pad-char-exp-'
The schema is SYSIBM.
The TRANSLATE function returns a value in which one or more characters in a string expression might have been converted to other characters.
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.
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.
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.
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 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.
Examples:
TRANSLATE(:SITE)
Returns
the value 'HANAUMA BAY'. TRANSLATE(:SITE, 'j', 'B')
Returns
the value 'Hanauma jay'. TRANSLATE(:SITE, 'ei', 'aa')
Returns
the value 'Heneume Bey'. TRANSLATE(:SITE, 'bA', 'Bay', '%')
Returns
the value 'HAnAumA bA%'. TRANSLATE(:SITE, 'r', 'Bu')
Returns
the value 'Hana ma ray'. TRANSLATE(:SITE, 'r', 'Bu', '')
Returns
the value 'Hanama ray'.