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. |
