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.21 Value expression.

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

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

character-to-replace:

Specifies the character to replace.

The following specification rules apply:

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

• Specify CHAR, VARCHAR, or STRING type data for character-to-replace.

• If two or more identical characters are specified as character-to-replace, the first character specified as the target character is valid.

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

replacement-character:

Specifies the replacement character.

The following specification rules apply:

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

• Specify CHAR, VARCHAR, or STRING type data for replacement-character.

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

Tip

When replacing multiple characters, specify so that the character positions from the beginning of character-to-replace and replacement-character are the same. For example, to replace A with a, B with b, and C with c, specify 'ABC' for character-to-replace and 'abc' for replacement-character.

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

Example:

Replace A with a, B with b, and C with c in the character string.

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)
   STRING	STRING

   Legend: n: Maximum length of the target data

2. If the replacement results in a data length that exceeds the length of the execution result, an error is generated. Therefore, 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.

   Example:

   The example assumes Unicode (UTF-8) as the character encoding.

   Column C1 has type VARCHAR(5) and holds the character string 'ABC'.

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

   In the above example, the data length of the execution result of TRANSLATE is VARCHAR(5), which results in an error due to insufficient data length.

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

   In the above example, the data length of the execution result of TRANSLATE is VARCHAR(9), so there is no error.

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

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

5. The character-to-replace in the target-data is replaced with the replacement-character. Characters in the target data that are not specified as replacement characters will not be replaced.

6. If the number of replacement-character is less than the number of character-to-replace, when an extra specified character-to-replace exists in the target data, that character-to-replace is removed from the target data.

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

7. If the number of replacement-character is greater than the number of character-to-replace, the extra specified replacement-character are ignored.

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

8. If the replacement result causes all the target character string data to be removed, the execution result will be data whose actual length is 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