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

character-string-to-replace:

Specifies the character string to be replaced.

The following rules apply:

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

• You must specify CHAR or VARCHAR 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 rules apply:

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

• You must specify CHAR or VARCHAR 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 whose actual length is 0 bytes is assumed.

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

Example

Replace all instances of the character string 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)

   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 column C1 has type VARCHAR(5) and contains the character string 'ABCD'.

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

   The above example results in an error because the data length of the result of executing REPLACE is too large for VARCHAR(5).

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

   The above example does not result in an error because the data length of the result of executing REPLACE can fit in VARCHAR(10).

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

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

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

6. If you specify data whose actual length is 0 bytes or 0 characters for the character string to replace, no characters in the target data are replaced.

7. If you specify data whose actual length is 0 bytes or 0 characters for the replacement character string, all instances of the character string to replace are removed from the target data.

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