REPLACE

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-REPLACE--(--source-string--,--search-string--,--replace-string--)-><
 

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
An expression that specifies the source string. The source-string must be a built-in numeric or string expression. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR.
search-string
An expression that specifies the string to be removed from the source string. The search-string must be a built-in numeric or string expression. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR.
replace-string
An expression that specifies the replacement string. The replace-string must be a built-in numeric or string expression. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR.

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:

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.

Examples