REPLACE

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 about 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 about 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 about converting numeric to a character string, see VARCHAR.
If replace-string is an empty string or is not specified, nothing replaces the string that is removed from the source string.

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:

If search-string is variable length, the length attribute of the result is:
```
   (L3 * L1)
```
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
Otherwise, the length attribute of the result is:
```
   (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.

Examples

Replace all occurrences of the character 'N' in the string 'DINING' with 'VID'. Use the CHAR function to limit the output to 10 bytes.
```
  SELECT CHAR(REPLACE( 'DINING', 'N', 'VID' ), 10)
    FROM SYSIBM.SYSDUMMY1
```
The result is the string 'DIVIDIVIDG'.
Replace string 'ABC' in the string 'ABCXYZ' with nothing, which is the same as removing 'ABC' from the string.
```
  SELECT REPLACE( 'ABCXYZ', 'ABC', '' )
    FROM SYSIBM.SYSDUMMY1
```
The result is the string 'XYZ'.
Omitting the third argument returns the same result.
```
  SELECT REPLACE( 'ABCXYZ', 'ABC')
    FROM SYSIBM.SYSDUMMY1
```
Replace string 'ABC' in the string 'ABCCABCC' with 'AB'. This example illustrates that the result can still contain the string that is to be replaced (in this case, 'ABC') because all occurrences of the string to be replaced are identified prior to any replacement.
```
  SELECT REPLACE( 'ABCCABCC', 'ABC', 'AB' )
    FROM SYSIBM.SYSDUMMY1
```
The result is the string 'ABCABC'.