SQL Reference

TRANSLATE

character string expression:

>>-TRANSLATE--(--char-string-exp-------------------------------->
 
>-----+---------------------------------------------------------+>
      |                                       .-,--' '-------.  |
      '-,--to-string-exp--,--from-string-exp--+--------------+--'
                                              '-,--pad-char--'
 
>----)---------------------------------------------------------><

graphic string expression:

>>-TRANSLATE--(--graphic-string-exp--,--to-string-exp--,--from-string-exp-->
 
     .-,--' '-------.
>----+--------------+--)---------------------------------------><
     '-,--pad-char--'

The schema is SYSIBM.

The TRANSLATE function returns a value in which one or more characters in a string expression may have been translated into other characters.

The result of the function has the same data type and code page as the first argument. The length attribute of the result is the same as that of the first argument. If any specified expression can be NULL, the result can be NULL. If any specified expression is NULL, the result will be NULL.

char-string-exp or graphic-string-exp

A string to be translated.

to-string-exp

Is a string of characters to which certain characters in the char-string-exp will be translated.

If the to-string-exp is not present and the data type is not graphic, all characters in the char-string-exp will be monocased (that is, the characters a-z will be translated to the characters A-Z, and characters with diacritical marks will be translated to their upper case equivalents if they exist. For example, in code page 850, é maps to É, but ÿ is not mapped since code page 850 does not include &Ye.).

from-string-exp

Is a string of characters which, if found in the char-string-exp, will be translated to the corresponding character in the to-string-exp. If the from-string-exp contains duplicate characters, the first one found will be used, and the duplicates will be ignored. If the to-string-exp is longer than the from-string-exp, the surplus characters will be ignored. If the to-string-exp is present, the from-string-exp must also be present.

pad-char-exp

Is a single character that will be used to pad the to-string-exp if the to-string-exp is shorter than the from-string-exp. The pad-char-exp must have a length attribute of one, or an error is returned. If not present, it will be taken to be a single-byte blank.

The arguments may be either strings of data type CHAR or VARCHAR, or graphic strings of data type GRAPHIC or VARGRAPHIC. They may not have data type LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, or DBCLOB.

With graphic-string-exp, only the pad-char-exp is optional (if not provided, it will be taken to be the double-byte blank), and each argument, including the pad character, must be of graphic data type.

The result is the string that occurs after translating all the characters in the char-string-exp or graphic-string-exp that occur in the from-string-exp to the corresponding character in the to-string-exp or, if no corresponding character exists, to the pad character specified by the pad-char-exp.

The code page of the result of TRANSLATE is always the same as the code page of the first operand, which is never converted. Each of the other operands is converted to the code page of the first operand unless it or the first operand is defined as FOR BIT DATA (in which case there is no conversion).

If the arguments are of data type CHAR or VARCHAR, the corresponding characters of the to-string-exp and the from-string-exp must have the same number of bytes. For example, it is not valid to translate a single-byte character to a multi-byte character or vice versa. An error will result if an attempt is made to do this. The pad-char-exp must not be the first byte of a valid multi-byte character, or SQLSTATE 42815 is returned. If the pad-char-exp is not present, it will be taken to be a single-byte blank.

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

Examples:

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]