REPLACE to Replace Characters in a String

July 27, 2011 Skip Marchesani

In V5R3, IBM simplified the manipulation of character strings with the implementation of the INSERT and REPLACE functions in SQL. INSERT allows the positional insertion of one or more characters in a string and REPLACE scans for all occurrences of a target string and overlays or replaces the target string with a replace string. INSERT was discussed in my previous article, and now I will discuss REPLACE.

Prior to the implementation of REPLACE in V5R3, scanning for the existence of a target string and replacing it with another string was not very easy. A positional replacement could be performed by creating a complex SQL statement that split a string into the appropriate pieces with SUBSTR, omitting the character(s) to be replaced, and inserting the replacement characters with CONCAT.

If the string needed to be scanned to search for a string to be replaced, an SQL procedure could be created–most likely using SUBSTR and CONCAT–that would find a target string and replace it with a replacement string.

In V5R3, the REPLACE function turns a complex SQL statement into a simple one. The REPLACE function scans a source string searching for a target string, and replaces all occurrences of the target string with a replace string. And, the replace string can be longer or shorter that the target string. If the target string is not found in the source string, the source string is returned without any change in the result set.

The syntax for the REPLACE function is simple:

REPLACE(source_string, target_string, replace_string)

To see how REPLACE works, let’s assume we have a name and address table that includes phone number with two hyphens and is defined as CHAR(12). The format of the phone number is AAA-EEE-NNNN; where AAA- is the area code, followed by a hyphen, EEE- is the phone exchange, followed by a hyphen, and NNNN is the phone number.

Following is a SELECT statement that will return NAME, COMPANY, and PHONE, and use REPLACE to remove the two hyphens from PHONE.

SELECT name, company, REPLACE(phone, '-', '') AS phone
	FROM namemstr
	ORDER BY name

In the above SELECT statement the REPLACE searched for all occurrences of a hyphen and removed the hyphens, or replaced them with nothing. To tell REPLACE to replace the target string with nothing, two single quote marks must be used together with no space between them. Therefore, don’t make the mistake of thinking that the ”, the third operand or expression that follows the ‘-‘ in the REPLACE, is a double quote mark.

Now let’s look at an example of using REPLACE to replace one or more characters in a string with an actual value instead of nothing. In the following table, the column name is 10 positions long, and we will replace all occurrences of a lowercase “k” with two uppercase Xs (XX).

Nbr	Nam
10	Ed
20	Heikki
30	John
40	Mike
50	Marcela
60	Frank

The SQL syntax to do the REPLACE is below and followed by the result of the REPLACE.

SELECT  nbr,  nam,  REPLACE (nam, 'k', 'XX') AS replace
     FROM  emp  ORDER BY  nbr;

Nbr	Nam	Replace
10	Ed	Ed
20	Heikki	HeiXXXXi
30	John	John
40	Mike	MiXXe
50	Marcela	Marcela
60	Frank	FranXX

Remember that the replace string does not have to be the same length as the target string: it can be longer or shorter. When the replace string is not the same length as the target string, the length of the source string will be adjusted accordingly, but cannot exceed the maximum length for the data type of the source string.

The length of the source string after a replace is the length of the replace string minus the length of the target string, multiplied by the number of occurrences of the target string, added to the original length of the source string.

((replace_string - target_string) * (occurrences_of_target) + 
original_source_string_length

REPLACE is case-sensitive and will differentiate between uppercase and lowercase characters when searching for the target string.

REPLACE provides a very easy way to replace all occurrences of a target string in a source string with a replace string.

Skip Marchesani retired from IBM after 30 years and decided to pursue a career as a consultant in the IBM i marketplace. He spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400, and was involved with the development of the AS/400. Skip was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Now recognized as an industry expert on DB2 for i (a.k.a. DB2/400), and author of the book “DB2/400: The New AS/400 Database,” Skip specializes in providing customized education for any area of the IBM i and AS/400, does database design and design reviews, and general IBM i, iSeries, and AS/400 consulting. He has been a speaker for user groups, technical conferences, and IBM i, iSeries, and AS/400 audiences around the world, and has received COMMON’s Distinguished Service Award. Skip Recently moved to Vermont and is now president of the Vermont Mid Range User Group (VTMUG). Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.