8.7.1 REPLACE

(1) Specification format

scalar-function-REPLACE ::= REPLACE(target-data,character-string-to-replace[,replacement-character-string])
 
  target-data ::= value-expression
  character-string-to-replace ::= value-expression
  replacement-character-string ::= 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-string-to-replace:

Specifies the character string to replace.

The following specification rules apply:

• Specify character-string-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-string-to-replace.

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

replacement-character-string:

Specifies the replacement character string.

The following specification rules apply:

• Specify replacement-character-string 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-string.

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

• If replacement-character-string is omitted, data with an actual length of 0 is assumed.

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

Example:

Replace all strings BCD in the target data with YZ.

REPLACE('ABCDEBCD','BCD','YZ') → 'AYZEYZ'

To Page Top

(3) Rules

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

   Table 8‒21: Data type and data length of the execution result of the scalar function REPLACE

   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:

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

   REPLACE("C1",'AB','WXYZ') → Error

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

   REPLACE(CAST("C1" AS VARCHAR(10)),'AB','WXYZ') → 'WXYZCD'

   In the above example, the data length of the execution result of REPLACE is VARCHAR(10), 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 either of the target data, character string to replace, or replacement character string is a null value, the execution result will be a null value.

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

6. If an actual length of 0 bytes or 0 characters is specified for the character string to replace, characters in the target data are not replaced.

7. If an actual length of 0 bytes or 0 characters is specified for the replacement character string, all of the character strings to replace in the target data are deleted.

To Page Top

(4) Example

Example:

This example assumes that column C1 (CHAR type) of table T1 holds dates in the format YYYY.MM.DD (where YYYY is the year, MM is the month, and DD is the day).

All instances of 2013 are replaced with 2014.

SELECT REPLACE("C1",'2013','2014') FROM "T1"

To Page Top