REGEXP_REPLACE: Replacing All Matches to a Pattern in a String

REGEXP_REPLACE returns a string generated by replacing all matches to a regular expression pattern in the source string with the given replacement string. The replacement string can be a null string.

Replace Matches to a Pattern in a String

REGEXP_REPLACE(string, pattern, replacement)

where:

string

Alphanumeric

Is the input string to be searched.

pattern

Alphanumeric

Is a regular expression, enclosed in single quotation marks, constructed using literals and meta-characters. The following meta-characters are supported

  • . represents any single character
  • * represents zero or more occurrences
  • + represents one or more occurrences
  • ? represents zero or one occurrence
  • ^ represents beginning of line
  • $ represents the end of the line
  • [] represents any one character in the set listed within the brackets
  • [^] represents any one character not in the set listed within the brackets
  • | represents the Or operator
  • \ is the Escape Special Character
  • () contains a character sequence
replacement

Alphanumeric

Is the replacement string.

Replacing Matches to a Pattern in a String

The following example uses the following Regular Expression symbol.

  • ^, which searches for a specified expression that occurs at the beginning of a string.

REGEXP_REPLACE replaces the characters 'ENG' at the beginning of the field COUNTRY with the replacement string 'SCOT'.

REGEXP_REPLACE(COUNTRY, '^ENG', 'SCOT')

For 'ENGLAND', the result is 'SCOTLAND'.

In the following request REG1 replaces the string 'North' at the beginning of the REGION field value with the string 'South', and REG2 replaces the string 'Mid' at the beginning of the REGION field value with a null string.

TABLE FILE GGSALES
SUM DOLLARS NOPRINT AND COMPUTE
REG1/A25 = REGEXP_REPLACE(REGION, '^North', 'South');
REG2/A25 = REGEXP_REPLACE(REGION, '^Mid', '');
BY REGION
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

The following version of the request runs on z/OS. The regular expression string is created in a variable using a -SET command. The circumflex meta-character (^) is inserted as CHAR(95).

-SET &REGSTRING1= CHAR(95) || 'North' ;                    
-SET &REGSTRING2= CHAR(95) || 'Mid' ;                      
TABLE FILE GGSALES                                         
SUM DOLLARS NOPRINT AND COMPUTE                            
REG1/A25 = REGEXP_REPLACE(REGION, '&REGSTRING1', 'South'); 
REG2/A25 = REGEXP_REPLACE(REGION, '&REGSTRING2', '');      
BY REGION                                                  
ON TABLE SET PAGE NOLEAD                                   
ON TABLE SET STYLE *                                       
GRID=OFF,$                                                 
ENDSTYLE                                                   
END

The output follows.

  Region       REG1                       REG2      
  ------       ----                       ----      
  Midwest      Midwest                    west      
  Northeast    Southeast                  Northeast 
  Southeast    Southeast                  Southeast 
  West         West                       West