Informatica Reference

REG_REPLACE

Posted on: 12/14/2011

Replaces characters in a string with another character pattern. By default, REG_REPLACE searches the input string for the character pattern you specify and replaces all occurrences with the replacement pattern. You can also indicate the number of occurrences of the pattern you want to replace in the string.

Syntax
REG_REPLACE( subject, pattern, replace, numReplacements )

Argument
Required/
Optional
Description
subject
Required
String datatype. Passes the string you want to search.
pattern
Required
String datatype. Passes the character string to be replaced. You must use perl compatible regular expression syntax. Enclose the pattern in single quotes. For more information, see “REG_EXTRACT” on page 113.
replace
Required String datatype. Passes the new character string.
numReplacements
Required
Numeric datatype. Specifies the number of occurrences you want to replace. If you omit this option, REG_REPLACE will replace all occurrences of the character string.

Return Value

String

Example

The following expression removes additional spaces from the Employee name data for each row of the Employee_name port:

REG_REPLACE( Employee_Name, ‘\s+’, ‘ ’)

Employee_Name                      Return Value
Adam Smith                                 Adam Smith
Greg      Sanders                         Greg Sanders
Sam            Cooper                      Sam Cooper

Advertisements
%d bloggers like this: