REPLACE scalar function

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.

Read syntax diagramSkip visual syntax diagramREPLACE(source-string ,search-string ,replace-string )

The schema is SYSIBM.

source-string
An expression that specifies the source string. The expression must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB and it cannot be an empty string.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

The actual length of source-string must be 32764 bytes or less for character and binary strings or 16382 or less for graphic strings.

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 character string, graphic string, or binary string data type that is not a LOB; the value cannot be an empty string.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

Start of changeThe actual length of search-string must be 4000 bytes or less for character and binary strings or 2000 or less for graphic strings, unless the CCISD of search-string is Unicode DBCS. If the CCSID is Unicode DBCS, the actual length must be 16382 or less.End of change

replace-string
An expression that specifies the replacement string. The expression must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

The actual length of replace-string must be 32764 bytes or less for character and binary strings or 16382 or less for graphic strings.

If replace-string is not specified or is an empty string, nothing replaces the string that is removed from the source string.

All three arguments must have compatible data types. If the expressions have different CCSID sets, then the expressions are converted to the CCSID set of source-string.

The data type of the result of the function depends on the data type of source-string, search-string, and replace-string:

  • VARCHAR if source-string is a character string. The encoding scheme of the result is the same as source-string. The CCSID of the result depends on the arguments:
    • If source-string, search-string, or replace-string is bit data, the result is bit data.
    • If source-string, search-string, and replace-string are all SBCS Unicode data, the CCSID of the result is the CCSID for SBCS Unicode data.
    • If source-string is SBCS Unicode data, and search-string or replace-string is not SBCS Unicode data, the CCSID of the result is the mixed CCSID for Unicode data.
    • Otherwise, the CCSID of the result is the mixed CCSID that corresponds to the CCSID of source-string. However, if the input is EBCDIC or ASCII and there is no corresponding system CCSID for mixed, the CCSID of the result is the CCSID of source-string.
  • VARGRAPHIC if source-string is a graphic. The encoding scheme of the result is the same as source-string. The CCSID of the result is the same as the CCSID of source-string.
  • VARBINARY if source-string, search-string, and replace-string are binary strings.

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 or VARBINARY:
      • 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(32764, (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(16382, (L3*(L1/L2)) + MOD(L1,L2))
    where:
    • L1 is the length attribute of source-string
    • L2 is the length attribute of 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 or binary string, the length attribute of the result must not exceed 32764. If the result is a graphic string, the length attribute of the result must not exceed 16382.

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 return data type, an error occurs.

The result can be null; if any argument is null, the result is the null value.

Examples

Example 1
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'.

Example 2
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'.
Example 3
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'.