REPLACE: Replacing a String

Syntax: How to Replace all Instances of a String

REPLACE(input_string , search_string , replacement)

where:

input_string

Alphanumeric or text (An, AnV, TX)

Is the input string.

search_string

Alphanumeric or text (An, AnV, TX)

Is the string to search for within the input string.

replacement

Alphanumeric or text (An, AnV, TX)

Is the replacement string to be substituted for the search string. It can be a null string ('').

Example: Replacing a String

REPLACE replaces the string 'South' in the Country Name with the string 'S.'

SET TRACEUSER = ON 
SET TRACEON = STMTRACE//CLIENT
SET TRACESTAMP=OFF
DEFINE FILE WF_RETAIL_LITE
NEWNAME/A20 = REPLACE(COUNTRY_NAME, 'SOUTH', 'S.');
END
TABLE FILE WF_RETAIL_LITE
SUM COUNTRY_NAME
BY NEWNAME AS 'New,Name'
WHERE COUNTRY_NAME LIKE 'S%'
ON TABLE SET PAGE NOLEAD
END

The generated SQL passes the REPLACE function to the DBMS REPLACE function.

SELECT   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.'),  
MAX(T3."COUNTRY_NAME")  
FROM   
wrd_wf_retail_geography T3  
WHERE   
(T3."COUNTRY_NAME" LIKE 'S%')  
GROUP BY   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.')  
ORDER BY   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.');

The output is shown in the following image.

Example: Replacing All Instances of a String

In the following request, the virtual field DAYNAME1 is the string DAY1 with all instances of the string 'DAY’ replaced with the string 'day'. The virtual field DAYNAME2 has all instances of the string 'DAY’ removed.

DEFINE FILE WF_RETAIL
DAY1/A30 = 'SUNDAY MONDAY TUESDAY';
DAYNAME1/A30 = REPLACE(DAY1, 'DAY', 'day' );
DAYNAME2/A30 = REPLACE(DAY1, 'DAY', '' );
END
TABLE FILE WF_RETAIL
PRINT DAY1 OVER
DAYNAME1 OVER
DAYNAME2
WHERE EMPLOYEE_NUMBER EQ 'AH118' 
ON TABLE SET PAGE NOPAGE
END

The output is:

  DAY1      SUNDAY MONDAY TUESDAY         
  DAYNAME1  SUNday MONday TUESday         
  DAYNAME2  SUN MON TUES