8.7.1 REPLACE
Replaces any character string in the target data. All instances of the character string to be replaced in the target data are replaced with a replacement character string.
- Organization of this subsection
(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
(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'
(3) Rules
-
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
-
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).
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If the target data, character string to replace, or replacement character string is the null value, the execution result will be the null value.
-
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.
-
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.
-
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.
(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"