@STRSUB

Use the @STRSUB function to substitute strings within a string (character) column or constant. Enclose literal strings within single quote marks.

For this function, Oracle GoldenGate supports the use of an escape sequence to represent characters in a string column in Unicode or in the native character encoding of the Microsoft Windows, UNIX, and Linux operating systems. The target column must be a SQL Unicode data type if any argument is supplied as Unicode.

Any single byte code value 1 to 255 can be used in hexadecimal or octal format for the string arguments. Hex values A to F are case insensitive and the leading 'x' must be lower case. Value zero (0) (\x00 and \000) is not allowed because it is a string terminator. No multibyte character set value or UNICODE values are supported.

This function does not support NCHAR or NVARCHAR data types.

Syntax

@STRSUB
(source_string, search_string, substitute_string
[, search_string, substitute_string] [, ...])

source_string: A source string, within single quotes, or the name of a source column that contains the characters for which substitution is to occur.
search_string: The string, within single quotes, for which substitution is to occur.
substitute_string: The string, within single quotes, that will be substituted for the search string.

Examples

Example 1

The following returns xxABCxx.

@STRSUB ('123ABC123', '123', 'xx')

Example 2

The following returns 023zBC023.

@STRSUB ('123ABC123', 'A', 'z', '1', '0')

Example 3

The following is an example of replacing ^Z, using a hexadecimal string argument, with a space.

@strsub (col1,'\x1A',' '));