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.
INSERT to Overlay Positions in a Character String
SQL Implicit Cast of Character Strings and Numeric Values
Use SQL to Remove Extra Spaces