Replaces
all occurrences of search-string in source-string with replace-string.
.-,''-------------.
>>-REPLACE--(--source-string--,--search-string--+-----------------+--)-><
'-,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 search-string is not found
in source-string, search-string is
returned unchanged. A linguistically correct search is done 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. 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 CHAR, VARCHAR, GRAPHIC,
VARGRAPHIC, numeric, or datetime data type. If the value is not a
CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly
cast to VARCHAR before evaluating the function.
- 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 CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric,
or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC,
or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating
the function.
- replace-string
- An expression that specifies the replacement string. The expression must return a value that is a built-in
CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type.
If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type,
it is implicitly cast to VARCHAR before evaluating the function. If
the expression is an empty string or is not specified, nothing replaces
the string that is removed from the source string.
The actual length of each string must be 32 672
bytes or less for character strings, or 16 336 or less for graphic
strings. All three arguments must have compatible data types.
If source-string, search-string or replace-string is
defined as FOR BIT DATA, the result is VARCHAR FOR BIT DATA. If source-string is
a character string, the result is VARCHAR. If source-string is
a graphic string, the result is VARGRAPHIC. If one argument is
character FOR BIT DATA, the other arguments must not be graphic (SQLSTATE
42846).
The length attribute of the result depends on the
arguments:
- If the length attribute of replace-string is
less than or equal to the length attribute of search-string,
the length attribute of the result is the length attribute of source-string.
- If the length attribute of replace-string is
greater than the length attribute of search-string,
the length attribute of the result is determined as follows, depending
on the data type of the result:
- For VARCHAR:
- 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))
- For VARGRAPHIC:
- 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))
where:
- L1 is the length attribute of source-string
- L2 is the length attribute of the search-string if
the search string is a string constant. Otherwise, L2 is 1.
- L3 is the length attribute of replace-string
If the result is a character string, the length attribute of
the result must not exceed 32 672. If the result is a graphic string,
the length attribute of the result must not exceed 16 336.
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 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'.
VALUES CHAR (REPLACE ('DINING', 'N', 'VID'), 10)
The
result is the string 'DIVIDIVIDG'.
- Example 2: In a Unicode database
with case-insensitive collation CLDR181_LEN_S1, replace the word 'QUICK'
with the word 'LARGE'.
VALUES REPLACE ('The quick brown fox', 'QUICK', 'LARGE')
The
result is the string 'The LARGE brown fox'.