DB2 10.5 for Linux, UNIX, and Windows

REPLACE scalar function

Replaces all occurrences of search-string in source-string with replace-string.


                                                .-,''-------------.      
>>-REPLACE--(--source-string--,--search-string--+-----------------+--)-><
                                                '-,replace-string-'

The schema is SYSIBM. The SYSFUN version of the REPLACE function continues to be available but it is not sensitive to the database collation.

If search-string is not found in source-string, search-string is returned unchanged. A linguistically correct search is done if the Unicode database is defined with a locale-sensitive UCA-based collation and none of the source-string, search-string or replace-string arguments are defined as FOR BIT DATA. Otherwise, the search is done using a binary comparison with no special consideration for multi-byte characters.

source-string: An expression that specifies the source string. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function.
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 CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function.
replace-string: An expression that specifies the replacement string. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function. If the expression is an empty string or is not specified, nothing replaces the string that is removed from the source string.

The actual length of each string must be 32 672 bytes or less for character strings, or 16 336 or less for graphic strings. All three arguments must have compatible data types.

If source-string, search-string or replace-string is defined as FOR BIT DATA, the result is VARCHAR FOR BIT DATA. If source-string is a character string, the result is VARCHAR. If source-string is a graphic string, the result is VARGRAPHIC. If one argument is character FOR BIT DATA, the other arguments must not be graphic (SQLSTATE 42846).

The string units of the result is the same as the string units of source-string. The length attribute of the result depends on the arguments:

The length attribute of the result is the length attribute of the source-string (with the same string units) in the following cases:
- The replace-string argument is not specified or is specified as an empty string constant.
- The search-string is a constant and the number of bytes in the search-string constant is greater than or equal to:
  - The number of bytes of a constant replace-string.
  - The length attribute of a non-constant character string type replace-string in OCTETS.
  - The length attribute times 2 of a non-constant graphic string type replace-string in double bytes or CODEUNITS16.
  - The length attribute times 4 of a non-constant replace-string in CODEUNITS32.
Otherwise, the length attribute of the result is determined by the following calculation depending on the data type of the result (that allows for the smallest possible search-string to be replaced by the largest possible replace-string):
- For VARCHAR with string units of OCTETS:
  - 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(32672, (L3*(L1/L2)) + MOD(L1,L2))
- For VARCHAR with string units of CODEUNITS32 the length attribute of the result is MIN(8168, (L3*(L1/L2)) + MOD(L1,L2)).
- For VARGRAPHIC with string units of double bytes or CODEUNITS16:
  - 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(16336, (L3*(L1/L2)) + MOD(L1,L2))
- For VARGRAPHIC with string units of CODEUNITS32 the length attribute of the result is MIN(8168, (L3*(L1/L2)) + MOD(L1,L2)).

where the length values for L1, L2, and L3 are given in the following table based on the string units of the result:

Table 1. Length values of L1, L2, and L3
String units of the result	L1	L2	L3
OCTETS (a character string)	Length attribute of `source-string`	One (1) if not a constant, otherwise the actual number of bytes in the constant expressed as a character string type.	If `replace-string` is a constant, the actual number of bytes in the constant expressed as a character string type. If `replace-string` is a character string in OCTETS then the length attribute of `replace-string`. If `replace-string` is a character string or a graphic string in CODEUNITS32, then 4 times the length attribute of `replace-string`. If `replace-string` is a graphic string in double bytes or CODEUNITS16, then 3 times the length attribute of `replace-string`.
CODEUNITS16 or double bytes (a graphic string)	Length attribute of `source-string`	One (1) if not a constant, otherwise the actual number of double bytes or CODEUNITS16 string units in the constant expressed as a graphic string type.	If `replace-string` is a constant, the actual number of double bytes or CODEUNITS16 string units in the constant expressed as a graphic string type. If `replace-string` is a character string in OCTETS or a graphic string in double bytes or CODEUNITS16 then the length attribute of `replace-string`. If `replace-string` is a character string or graphic string in CODEUNITS32 then the 2 times the length attribute of `replace-string`.
CODEUNITS32 (a character string or a graphic string)	Length attribute of `source-string`	One (1) if not a constant, otherwise length attribute of `search-string`.	Length attribute of `replace-string`

If the result is a character string, the length attribute of the result must not exceed the maximum length of the VARCHAR data type in the string units of the result. If the result is a graphic string, the length attribute of the result must not exceed the maximum length of the VARGRAPHIC data type in the string units of the result.

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 replace-string exceeds the maximum for the return 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.

Examples

Example 1: Replace all occurrences of the letter 'N' in the word 'DINING' with 'VID'.
```
   VALUES CHAR (REPLACE ('DINING', 'N', 'VID'), 10)
```
The result is the string 'DIVIDIVIDG'.
Example 2: In a Unicode database with case-insensitive collation CLDR181_LEN_S1, replace the word 'QUICK' with the word 'LARGE'.
```
VALUES REPLACE ('The quick brown fox', 'QUICK', 'LARGE')
```
The result is the string 'The LARGE brown fox'.