REPLACE (SYSFUN schema) scalar function
Replaces all occurrences of expression2 in expression1 with expression3.
The schema is SYSFUN.
The search is done using a binary comparison with no special consideration for multi-byte characters.
- expression1 or expression2 or expression3
- The data type for the arguments can be of any built-in character string or binary string type.
The expression must return a built-in character string, Boolean value, or binary string. In a Unicode database, the expression can also return a graphic string, in which case it is first converted to a character string before the function is evaluated. The maximum length is:
- 4000 bytes for a VARCHAR
- 1,048,576 bytes for a CLOB or binary string
Result
The data type of the result is VARCHAR(4000).
The result can be null; if any argument is null, the result is the null value.
Examples
Replace all occurrences of the letter 'N' in the word 'DINING' with
'VID':
VALUES REPLACE ('DINING', 'N', 'VID')
This example
returns the following VARCHAR(4000) output:
1
----------
DIVIDIVIDG
Replace all occurrences of the letter 'N' in the word 'DINING' with 'VID', and limit the output
to 9
bytes:
VALUES CHAR (REPLACE ('DINING', 'N', 'VID'), 9)
This
example returns the following CHAR(9) output:
1
----------
DIVIDIVID