REPLACE( ) function

Replaces all instances of a specified character string with a new character string.


REPLACE(string, old_text, new_text)



Character. The value to replace characters in.


Character. The character string to replace.


Character. The text to replace the value in old_text with.




The REPLACE( ) function replaces every instance of an existing string with a new string. For example, the output for REPLACE("1234 Scott rd.", "rd.", "Road") is “1234 Scott Road”. You can also use REPLACE( ) to remove a specified character string from a source string, by replacing it with an empty character string ("").

The REPLACE ( ) function is case-sensitive. If you specify "RD." in old_text and the values in string are lowercase, the new_text value will not be substituted because no matches will be found. If there is a chance the case in string may be mixed, first use the UPPER( ) function to convert all characters to uppercase.

When building a REPLACE( ) expression you must be aware of every possible instance of old_text in string so that you do not get inadvertent replacements. For example, the output for REPLACE("645 Richard rd  ", "rd", "Road") is “645 RichaRoad Road” because the last two letters of Richard are “rd”. By adding both a leading space and a trailing space to the value in old_text (" rd "), you prevent the function from replacing instances where “rd” occurs in a name, because in these instances there are no leading spaces. For example, the output for REPLACE("645 Richard rd ", " rd ", " Road") is “645 Richard Road”.

You can use REPLACE( ) for normalizing data fields with inconsistent formats, such as address fields, or for replacing invalid information in poorly edited fields. To be performed accurately, operations such as duplicates testing, or joining or relating tables, require data with a normalized or standardized format.

REPLACE( ) returns a fixed-length string in which each occurrence of old_text in string is replaced by new_text. ACL automatically increases the field length to accommodate a single replacement. The maximum field length is either the length of string, or the length of (string - old_text + new_text) – whichever of these two is greater in length. If the resulting string is shorter than the source string, blanks are added to the end of the resulting string to make up the difference.

However, if multiple replacements occur, the new contents of the data field can exceed the field length. When this happens, ACL truncates data at the end of the field. To avoid loss of information, ensure that the field is long enough to accommodate the possible increase in data length.


Example Return value







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

REPLACE("abc/abc/abc","b","32") = "a32c/a32c/a3"

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

You can increase the length of the string parameter to accommodate the length of a returned string. Use either the BLANKS( ) function, or a string literal, with the appropriate number of additional spaces to lengthen the string parameter.

For example:

REPLACE("abc/abc/abc"+BLANKS(2),"b","32") or

REPLACE("abc/abc/abc"+"  ","b","32")

both return “a32c/a32c/a32c”.

Related reference
BLANKS( ) function

(C) 2015 ACL Services Ltd. All Rights Reserved.