REPLACE( ) function
Replaces all instances of a specified character string with a new character string.
Syntax
REPLACE(string, old_text, new_text)
Parameters
| Name | Type | Description |
|---|---|---|
| string |
character |
The value to replace characters in. |
| old_text |
character |
The character string to replace. The search is case-sensitive. |
| new_text | character | The text to replace the value in old_text with. |
Output
Character.
Examples
Basic examples
Returns "a12345efg":
REPLACE("abcdefg","bcd","12345")
Returns "Rd.":
REPLACE("Road","Road","Rd.")
Returns "ac":
REPLACE("abc","b","")
Advanced examples
Removing specified characters
Use REPLACE( ) to remove a specified character string from a source string, by replacing it with an empty character string ( "" ).
Returns "1234 Scott":
REPLACE("1234 Scott rd.", "rd.", "")
Field length adjustment
If new_text ("ABC") is longer than old_text ("X"), the field length of the resulting string is automatically increased to accommodate the first replacement:
Returns "9ABC9", with a field length increased to 5 characters from 3 characters:
REPLACE("9X9", "X", "ABC")
The field length is not automatically increased for subsequent replacements, and truncation can result if the field is not long enough to accommodate all the new characters.
Returns "9ABC9A":
REPLACE("9X9X", "X", "ABC")
To avoid truncation, you can increase the length of string using the BLANKS( ) function, or literal blank spaces.
Returns "9ABC9ABC":
REPLACE("9X9X" + BLANKS(2), "X", "ABC")REPLACE("9X9X" + " ", "X", "ABC")
If the resulting string is shorter than string, the resulting string is padded with blanks to maintain the same field length.
Returns "9X9 ":
REPLACE("9ABC9", "ABC", "X")
Remarks
How it works
The REPLACE( ) function replaces every instance of an existing string with a new string.
Returns "1234 Scott Road":
REPLACE("1234 Scott rd.", "rd.", "Road")
When to use REPLACE( )
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.
Case sensitivity
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.
Returns "1234 SCOTT ROAD":
REPLACE(UPPER("1234 Scott rd."), "RD.", "ROAD")
Protecting against inadvertent replacements
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.
Returns "645 RichaRoad Road", because the last two letters of "Richard" are "rd":
REPLACE("645 Richard rd ", "rd", "Road")
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.
Returns "645 Richard Road":
REPLACE("645 Richard rd ", " rd ", " Road")
