8.7.2 TRANSLATE

(1) Specification format

scalar-function-TRANSLATE ::= TRANSLATE(target-data,characters-to-replace,replacement-characters)
 
  target-data ::= value-expression
  characters-to-replace ::= value-expression
  replacement-characters ::= value-expression

To Page Top

(2) Explanation of specification format

target-data:

Specifies the target data.

The following rules apply:

• Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify CHAR or VARCHAR type data for the target data.

• You cannot specify a dynamic parameter by itself for the target data.

characters-to-replace:

Specifies the characters to be replaced.

The following rules apply:

• Specify characters-to-replace in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• If you specify the same character more than once in characters-to-replace, it uses the character that was specified first.

• If a dynamic parameter is specified by itself for characters-to-replace, the assumed data type of the dynamic parameter is VARCHAR(32000).

replacement-characters:

Specifies the replacement characters.

The following rules apply:

• Specify replacement-characters in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• If a dynamic parameter is specified by itself for replacement-characters, the assumed data type of the dynamic parameter is VARCHAR(32000).

Tip

To replace multiple characters, align the characters in the same positions in characters-to-replace and replacement-characters. For example, to replace A with a, B with b, and C with c, specify 'ABC' for characters-to-replace, and 'abc' for replacement-characters.

The following example illustrates the result of executing the scalar function TRANSLATE.

Example

In a character string, replace A with a, B with b, and C with c.

TRANSLATE('AXBYCZ','ABC','abc') → 'aXbYcZ'

To Page Top

(3) Rules

1. The data type and data length of the execution result are shown in the following table.

   Table 8‒22: Data type and data length of the execution result of the scalar function TRANSLATE

   Data type and data length of the target data	Data type and data length of the execution result
   CHAR(n)	VARCHAR(n)
   VARCHAR(n)

   Legend:

   n: Maximum length of the target data

2. An error results if, after the replacement, the data length of the execution result is exceeded. If you want to increase the data length of the execution result, use the scalar function CAST to change the data length of the target data.

   Examples

   These examples assume that Unicode (UTF-8) is the character encoding, and that column C1 has type VARCHAR(5) and holds the character string 'ABC'.

   TRANSLATE("C1",'ABC',' ') → Error

   This example generates an error because the type VARCHAR(5) is insufficient to store the data length of the execution result of TRANSLATE.

   TRANSLATE(CAST("C1" AS VARCHAR(9)),'ABC',' ') → ' '

   This time there is no error because the data length of the execution result of TRANSLATE is VARCHAR(9).

3. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

4. If target-data, characters-to-replace, or replacement-characters is the null value, the execution result will be the null value.

5. In the target data, the characters in characters-to-replace are replaced with the characters in replacement-characters. If no characters are specified in characters-to-replace, no characters in the target data are replaced.

6. When characters-to-replace is longer than replacement-characters, the extra characters in characters-to-replace are deleted from the target data if they are present.

   Example: TRANSLATE('ABCD','ABC','ab') → 'abD'

7. When characters-to-replace is shorter than replacement-characters, the extra characters in replacement-characters are ignored.

   Example: TRANSLATE('ABCD','AB','abc') → 'abCD'

8. If all the characters in the target data are deleted as a result of the replacement, the actual data length of the execution result will be 0 bytes.

To Page Top

(4) Example

Example:

Translate the format of the dates that are stored in column C1 (type CHAR) in table T1 from YYYY.MM.DD to YYYY/MM/DD, where YYYY is the year, MM is the month, and DD is the day.

SELECT TRANSLATE("C1",'.','/') FROM "T1"

To Page Top