The REPLACE function replaces all occurrences of search-string in source-string with replace-string. If search-string is not found in source-string, source-string is returned unchanged.
source-string, search-string, and replace-string must be compatible. For more information about data type compatibility, see Assignments and comparisons.
The data type of the result of the function depends on the data type of the arguments. The result data type is the same as if the three arguments were concatenated except that the result is always a varying-length string. For more information see Conversion rules for operations that combine strings.
The length attribute of the result depends on the arguments:
(L3 * L1)
(L3 * (L1/L2)) + MOD(L1,L2)
where:
L1 is the length attribute of source-string L2 is the length attribute of search-string L3 is the length attribute of replace-string
If the length attribute of the result exceeds the maximum for the result data type, an error is returned.
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 string exceeds the maximum for the result 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.
The CCSID of the result is determined by the CCSID of source-string, search-string, and replace-string. The resulting CCSID is the same as if the three arguments were concatenated. For more information, see Conversion rules for operations that combine strings.
SELECT CHAR(REPLACE( 'DINING', 'N', 'VID' ), 10), FROM SYSIBM.SYSDUMMY1The result is the string 'DIVIDIVIDG'.
SELECT REPLACE( 'ABCXYZ', 'ABC', '' ) FROM SYSIBM.SYSDUMMY1The result is the string 'XYZ'.
SELECT REPLACE( 'ABCCABCC', 'ABC', 'AB') ) FROM SYSIBM.SYSDUMMY1The result is the string 'ABCABC'.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.