Character substitution—%REPLACE
Use this function when you want CDC Replication to replace leading, trailing, or all occurrences of a specified character with another character during replication.
You can use this function to replace leading blank characters with zeros.
Syntax
%REPLACE(value, type
, original
, new
)Parameters
- value
- Specifies a character column, literal, or column function that returns a character string.
- type
- Specifies the substitution type. You must enclose values of this
parameter in double quotation marks.
- *ALL
- Replaces all occurrences of the specified character.
- *TRAIL
- Replaces all trailing occurrences of the specified character.
- *LEAD
- Replaces all leading occurrences of the specified character.
- original
- Specifies the set of characters to be replaced.
- new
- Specifies the set of characters to replace those specified in original. If the number of characters in original is greater than that in new , the extraneous characters in original are deleted in the result. If the number of characters in original is less than that in new the extraneous characters in new are ignored. If you specify new as two consecutive double quotation marks () , you can remove instances of a character from original.
Result data type
Character. Returns NULL if value is NULL.
Examples
%REPLACE(CUSTNO, *LEAD
,
, 0
)Replaces all leading blank characters in the CUSTNO column with zeros.
%REPLACE(CUSTNO, *LEAD
, ABC
, 123
)Replaces
all leading occurrences of A
with 1
, B
with 2
,
and C
with 3
in the CUSTNO column. Evaluation begins
with the first character and continues until a character other than A
, B
or C
is
found. For example, if a column value is AC7777
, this example
returns 137777
. If a column value is ADC7777
, this example
returns 1DC7777
.
%REPLACE(PHONENO, *ALL
,
, )Removes all blank characters in the PHONENO column. This example illustrates how the %REPLACE function can be used to remove a character from a string instead of replacing it with another character.
%REPLACE(PHONENO, *LEAD
,
, )Removes all leading blank characters in the PHONENO column. This example is similar to the previous example, except that only leading blank characters are removed. To remove leading blank characters, you can also use the %LTRIM function. To remove trailing blank characters, use the %RTRIM function.
%REPLACE(PARTNO, *TRAIL
, ACY
, acy
)Replaces
all trailing occurrences of A
with a
, C
with c
,
and Y
with y
in the PARTNO column. Evaluation begins
with the last character and continues until a character other than A
, C
or Y
is
found. For example, if a column value is 2361ACY
, this example
returns 2361acy
. If a column value is 2361ADY
, this
example returns 2361ADy
.
%REPLACE(259899
, *ALL
, 29
, 4
)Returns 458
.
Replaces all occurrences of 2
with 4
, and removes all
occurrences of 9
.
%REPLACE(259899
, *ALL
, 2
, 45
)Returns 459899
.
Replaces all occurrences of 2
with 4
. Does not remove
occurrences of 5
.
%REPLACE(PRODDESC, *ALL
, <<0>><<13>>
, <<32>><<32>>
)On platform that use the ASCII character set, this functions replaces all occurrences of NUL and carriage return in the PRODDESC column with blank characters.