MS Access Replace() Function: Syntax, Examples, & Practical Uses

Master the MS Access `Replace()` function. This guide provides a clear definition, practical examples, and explains how to use the `Replace()` function for substring manipulation, including specifying the number of replacements and string comparison options. Learn how to effectively find and replace text within your Access databases.

Definition and Usage

The Replace() function in MS Access is used to find and replace occurrences of a substring within a string. You can specify how many replacements to make and the type of string comparison to use.

Syntax


Replace(string1, find, replacement, start, count, compare)

Parameter Values

Parameter	Description
`string1`	Required. The original string where the replacement will occur.
`find`	Required. The substring to search for within `string1`.
`replacement`	Required. The substring that will replace `find`.
`start`	Optional. The starting position within `string1` to begin the search (default is 1).
`count`	Optional. The number of occurrences of `find` to replace (default is all occurrences).
`compare`	Optional. Specifies the comparison type (see below).

Compare Parameter Values

The compare parameter controls the type of string comparison:

-1: Uses the setting of Option Compare (default setting in your Access application).
0: Binary comparison (case-sensitive).
1: Textual comparison (case-insensitive).
2: Comparison based on the information in your database.

Example

Example: Replacing a Substring

This example replaces all occurrences of "i" with "a" in the string "My name is Willy Wonka".

SQL Query


SELECT Replace("My name is Willy Wonka", "i", "a") AS ReplaceString;

Output


ReplaceString
-------------
My name as Wally Wonka

Technical Details

The Replace() function is available in MS Access 2000 and later versions.