REPLACE()

REPLACE() replaces all instances of a specified character string with a new character string. Multiple replacements can also be specified as consecutive pair sets with the first item replaced by the second item in each pair set. Pair set replacements are evaluated in the order that they are entered in the REPLACE() function from left to right.

Use REPLACE() for normalizing data fields with inconsistent formats, such as address fields or for replacing invalid information in poorly edited fields. Another use of REPLACE() is to ensure formatting consistency where identical key fields are required (for example duplicate testing and Joining or Relating tables).

You can also use REPLACE() to remove a specified character string from a source string, by replacing it with an empty character string (“”).

Function Format

REPLACE(C1,C2,C3 <,C4,C5...>)

Note: Multiple replacement pair sets can be specified and each pair set replacement is fully evaluated prior to moving on to the next pair set. In the function syntax, the optional (C4,C5 ) pair set represents a sequence of one or more replacement pair set strings. Character comparisons are case-sensitive.

REPLACE() returns a fixed-length string in which each occurrence of C2 in C1 is replaced by C3. Analyzer automatically increases the field length to accommodate a single replacement for each specified replacement pair set.

The maximum field length is the greater of the length of C1 or the length of (C1 - C2 + C3). If the resulting string is shorter than the source string C1, blanks are added to the end of the string to make up the difference.

However, with multiple replacements for a given pair set, the new contents of the data field could exceed the original field length. When this happens, Analyzer truncates data at the end of the field. To avoid the loss of information, ensure that the field is long enough to accommodate the possible increase in data length. Use the BLANKS() function to concatenate blanks to the end of the string to ensure sufficient length to avoid truncation as shown in the example below.

Examples

In the following examples, a single replacement is made:

REPLACE("abcdefg","bcd","12345") = "a12345efg"

REPLACE("Road","Road","Rd.") = "Rd. "

REPLACE("abc","b","") = "ac "

In the following example, the returned string is truncated because multiple replacements make the returned string C3 longer than string C2:

REPLACE("abc/abc/abc","b","12") = "a12c/a12c/a1"

In this case, Analyzer increased the length of the field from 11 to 12 bytes to allow for one replacement (11 - 1 + 2 = 12). Because there were three replacements, the returned string is 2 bytes longer (11 - 1 + 2 - 1 + 2 - 1 + 2 = 14) and the last 2 bytes of the returned string are truncated.

The best way to increase the field length to accommodate the increased length of the returned string is to use the BLANKS() function to add blanks to the source string before you use REPLACE():

REPLACE("abc/abc/abc”+BLANKS(2),"b","12") = "a12c/a12c/a12c"

The number of blanks specified will depend on the anticipated longest length of the resulting string.

The following example shows a replacement using two pair sets:

REPLACE("abcdefg","bc","uvw","ef","xyz") = "auvwdxyzg "