Returns a String in which a specified substring has been replaced with another substring a specified number of times.
Syntax
Replace( expression, find, replace [, start ] [, count ] [, compare ] )
The Replace function syntax has these arguments:
Argument |
Description |
expression |
Required. String expression containing substring to replace. |
find |
Required. Substring being searched for. |
replace |
Required. Replacement substring. |
start |
Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed. |
count |
Optional. Number of substring substitutions to perform. If omitted, the default value is –1, which means make all possible substitutions. |
compare |
Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values. |
Settings
The compare argument can have the following values:
Constant |
Value |
Description |
vbUseCompareOption |
–1 |
Performs a comparison using the setting of the Option Compare statement. |
vbBinaryCompare |
0 |
Performs a binary comparison. |
vbTextCompare |
1 |
Performs a textual comparison. |
vbDatabaseCompare |
2 |
Microsoft Office Access 2007 only. Performs a comparison based on information in your database. |
Return Values
Replace returns the following values:
If |
Replace returns |
expression is zero-length |
Zero-length string ("") |
expression is Null |
An error. |
find is zero-length |
Copy of expression. |
replace is zero-length |
Copy of expression with all occurences of find removed. |
start > Len(expression) |
Zero-length string. String replacement begins at the position indicated by start. |
count is 0 |
Copy of expression. |
Remarks
The return value of the Replace function is a string, with substitutions made, that begins at the position specified by start and concludes at the end of the expression string. It is not a copy of the original string from start to finish.
Example
Expression |
Results |
SELECT ProductID, Replace(ProductID,"PRO","Product") AS ReplacedID FROM ProductSales; |
Returns the "ProductID", finds "PRO" in "ProductID" and replaces with "Product" and displays the result in column ReplacedID. |