SUBSTITUTE

The SUBSTITUTE function in Infoveave replaces all occurrences of a specified substring within a text string with another substring.

Applicable to

Calculated Columns

Return Value

The SUBSTITUTE function returns a new text string where all occurrences of search within expression are replaced.

Remark

• The SUBSTITUTE function is case-sensitive.
• If search is not found in expression, the function returns the original expression unchanged.
• SUBSTITUTE function supports specific character replacement. If not mentioned, it replaces the first instance of the character occurrence.

Syntax

SUBSTITUTE(<expression>, search, replace)

SUBSTITUTE in Calculated Columns

Parameter	Description
Expression	A placeholder in a function that is replaced with the column names.
Search	The substring you want to substitute.
Replace	The substring that will substitute your search.
Chars	The occurrence of substring you want to substitute.

Steps to Use

1. Write the SUBSTITUTE function. For instance SUBSTITUTE(<expression>, search, replace)
2. Replace <expression> with VALUE([Column Name])
3. Replace the Column Name with the actual name of your column required.
4. To learn how to add calculated columns in Infoveave, visit the section Calculated Columns.

Example 1

Objective Replace the ampersand symbol with the text value.

ID	TEXT
1	Apples & Oranges
2	Bread & Butter
3	Salt & Pepper

Use the SUBSTITUTE function like

SUBSTITUTE(VALUE([TEXT]), "&", "and")

The new calculated column “Updated Text” will return

ID	TEXT	UPDATED TEXT
1	Apples & Oranges	Apples and Oranges
2	Bread & Butter	Bread and Butter
3	Salt & Pepper	Salt and Pepper

Example 2

The SUBSTITUTE function in Infoveave can also substitute values on specified occurrences.

For example

SUBSTITUTE(VALUE([COUNTRY]), "a", "e", 2)

The SUBSTITUTE function will find the second occurrence of the letter “a” in the text from the [COUNTRY] column and replace it with the letter “e”.