DB2 Version 9.7 for Linux, UNIX, and Windows

TRANSLATE scalar function

character string expression:


>>-TRANSLATE--(--char-string-exp-------------------------------->

>--+-----------------------------------------------------------+-->
   |                                       .-,--' '----------. |   
   '-,--to-string-exp--,--from-string-exp--+-----------------+-'   
                                           '-,--pad-char-exp-'     

>--)-----------------------------------------------------------><

graphic string expression:


>>-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.

char-string-exp or graphic-string-exp: Specifies a 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: Specifies a string of characters to which certain characters in char-string-exp will 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: Specifies a string of characters which, if found in char-string-exp, will 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: Specifies a single character that will be 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 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:

Assume that the host variable SITE (VARCHAR(30)) has a value of 'Hanauma Bay'.
```
   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'.