REPLACE scalar function
Replaces all occurrences of search-string in source-string with replace-string.
The schema is SYSIBM. The SYSFUN version of the REPLACE function continues to be available but it is not sensitive to the database collation.
If the search string is not found in the source string, the search string is returned unchanged. If the Unicode database is defined with a locale-sensitive UCA-based collation and none of the source-string, search-string, or replace-string arguments are defined as FOR BIT DATA or as a binary string, a linguistically correct search is done. Otherwise, the search is done using a binary comparison with no special consideration for multi-byte characters.
-
source-string
- An
expression that specifies the source string. The expression must return a value that is a built-in
character string, numeric value, DBCLOB value, Boolean value, or datetime value. If the value is:
- A numeric, datetime, or CLOB value, it is implicitly cast to VARCHAR before the function is evaluated
- A DBCLOB value, it is implicitly cast to VARGRAPHIC before the function is evaluated
search-string
- An
expression that specifies the string to be removed from the source string. The expression must
return a value that is a built-in character string, numeric value, DBCLOB value, Boolean value, or datetime value.
If the value is:
- A numeric, datetime, or CLOB value, it is implicitly cast to VARCHAR before the function is evaluated
- A DBCLOB value, it is implicitly cast to VARGRAPHIC before the function is evaluated
replace-string
- An
expression that specifies the replacement string. The expression must return a value that is a
built-in character string, numeric value, DBCLOB value, Boolean value, or datetime value. If the value is:
- A numeric, datetime, or CLOB value, it is implicitly cast to VARCHAR before the function is evaluated
- A DBCLOB value, it is implicitly cast to VARGRAPHIC before the function is evaluated
All three arguments must have compatible data types.
Result
- If any argument is a BLOB, the result is a BLOB.
- If the arguments are binary strings and not BLOB, the result is a VARBINARY.
- If the arguments are character strings, the result is a VARCHAR. If any argument is defined as FOR BIT DATA, the result is defined as FOR BIT DATA.
- If the arguments are graphic strings, the result is a VARGRAPHIC.
In a Unicode database, if both character strings and graphic strings are used as arguments, then the result data type is based on the string type of the source-string argument. If the source-string argument is a character string type, then the result data type is VARCHAR. If the source-string argument is a graphic string type, then the result data type is VARGRAPHIC.
The string unit of the result is the string unit of source-string. If any argument is defined as FOR BIT DATA, the other arguments cannot be defined with string units of CODEUNITS32.
- The length attribute of the result is the length attribute of the source-string (with the same
string units) in the following cases:
- The replace-string argument is not specified or is specified as an empty string constant.
- The search-string is a constant and the number of bytes in the search-string constant is greater
than or equal to:
- The number of bytes of a constant replace-string.
- The length attribute of a non-constant character string type replace-string in OCTETS.
- The length attribute times 2 of a non-constant graphic string type replace-string in double bytes or CODEUNITS16.
- The length attribute times 4 of a non-constant replace-string in CODEUNITS32.
- The length attribute of a non-constant binary string type replace-string.
- Otherwise, the length attribute of the result is determined by the following calculation
depending on the data type of the result (that allows for the smallest possible
search-string to be replaced by the largest possible
replace-string):
- For VARCHAR with string units of OCTETS:
- If L1 < = 4000, the length attribute of the result is
MIN(4000, (L3*(L1/L2)) + MOD(L1,L2))
- Otherwise, the length attribute of the result is
MIN(32672, (L3*(L1/L2)) + MOD(L1,L2))
- If L1 < = 4000, the length attribute of the result is
- For VARCHAR with string units of CODEUNITS32, the length attribute of the result is
MIN(8168, (L3*(L1/L2)) + MOD(L1,L2))
. - For VARGRAPHIC with string units of double bytes or CODEUNITS16:
- If L1 < = 2000, the length attribute of the result is
MIN(2000, (L3*(L1/L2)) + MOD(L1,L2))
- Otherwise, the length attribute of the result is
MIN(16336, (L3*(L1/L2)) + MOD(L1,L2))
- If L1 < = 2000, the length attribute of the result is
- For VARGRAPHIC with string units of CODEUNITS32, the length attribute of the result is
MIN(8168, (L3*(L1/L2)) + MOD(L1,L2))
. - For
VARBINARY, the length attribute of the result is
MIN(32672, (L3*(L1/L2)) + MOD(L1,L2))
- For BLOB, the
length attribute of the result is
MIN(2G, (L3*(L1/L2)) + MOD(L1,L2))
- For VARCHAR with string units of OCTETS:
L1
, L2
, and L3
are given in
the following table based on the string units of the
result:
String units of the result | L1 | L2 | L3 |
---|---|---|---|
OCTETS (a character string) | Length attribute of source-string | One (1) if not a constant, otherwise the actual number of bytes in the constant expressed as a character string type. |
|
CODEUNITS16 or double bytes (a graphic string) | Length attribute of source-string | One (1) if not a constant, otherwise the actual number of double bytes or CODEUNITS16 string units in the constant expressed as a graphic string type. |
|
CODEUNITS32 (a character string or a graphic string) | Length attribute of source-string | One (1) if not a constant, otherwise length attribute of search-string. | Length attribute of replace-string |
Not applicable (a binary string) | Length attribute of source-string | One (1) if not a constant, otherwise the actual number of bytes in the constant expressed as a binary string type. |
|
If the result is a character string, the length attribute of the result must not exceed the maximum length of the VARCHAR data type in the string units of the result. If the result is a graphic string, the length attribute of the result must not exceed the maximum length of the VARGRAPHIC data type in the string units of the result.
The actual length of the result is the actual length of source-string plus the number of occurrences of search-string that exist in source-string multiplied by the actual length of replace-string minus the actual length of search-string. If the actual length of the result would exceed the length attribute of the result or would exceed 1,048,576 bytes, an error is returned (SQLSTATE 22001).
If the actual length of the replace-string exceeds the maximum for the return data type, an error is returned. If any argument can be null, the result can be null; if any argument is null, the result is the null value.
Examples
- Example 1: Replace all occurrences of the letter 'N' in
the word 'DINING' with 'VID'.
The result is the string 'DIVIDIVIDG'.VALUES CHAR (REPLACE ('DINING', 'N', 'VID'), 10)
- Example 2: In a Unicode database
with case-insensitive collation CLDR181_LEN_S1, replace the word 'QUICK'
with the word 'LARGE'.
The result is the string 'The LARGE brown fox'.VALUES REPLACE ('The quick brown fox', 'QUICK', 'LARGE')