The SUBSTITUTE function replaces all occurrences of a text substring in a text with another text substring. It can also be used to replace only a specific occurrence of the searched text substring.
SUBSTITUTE(
text,
old_text,
new_text
[, instance_num]
)
- text: Text string in which you want to replace a text substring with another, or name of the column that contains the texts in which to perform the substitution.
- old_text: Substring to replace.
- new_text: Substring with which you want to replace old_text.
- instance_num: Optional argument. Occurrence number of old_text to be replaced. If omitted, the replacement will be made for all occurrences.
The SUBSTITUTE function returns the text string text in which all occurrences (or a specific occurrence) of the text old_text have been replaced by new_text.
This function does not accept the use of wildcards in the old_text argument.
If old_text is not found in text, no replacement is done. In fact, the function is case and accent sensitive. In the following example we try to replace an "Á" with a "_" , but the character in question is not found, so the original text string is not modified:
The length of the text to replace (old_text) and the length of the text to be replaced by (new_text) do not have to be the same, which means that the length of the value returned by this function does not have to match the length of text. Thus, in the following example, the blank space is replaced by the string "* _ *", returning a text with a length greater than that of the original product name:
If old_text is an empty string, no replacement is done. Conversely, if new_text is an empty string, old_text is removed without being replaced by any other text string. In this example, the string "aximus" is removed to leave only the initial of the category and the last three characters that indicate the product code:
instance_num must be a strict integer greater than 0. Otherwise the function will return an error. If this argument is greater than the number of occurrences of the text to be replaced, no replacement is performed.
In the following example, we want to use as product code its name in lowercase, replacing all possible blanks with an underscore:
In this other example we want to extract from the product name its initial and the characters that follow the blank space, regardless of the length of the text that precedes or follows this space. To do this, the position of the blank space is calculated and the string is extracted from the second character to that position (replacing it with an empty text string). Finally all the returned text is converted to lowercase:
