SUBSTITUTE

SUBSTITUTE replaces a segment of a string with another string.

Function category: Text

See REPLACE for a similar string operation that can be done based on character indexes.

Syntax

SUBSTITUTE(arg1, arg2, arg3, arg4)

Arguments

Description

arg1

String or block of text to be searched.

arg2

Search string.

arg3

String with which to replace the search string.

arg4

Occurrence number.

Examples

Example 1: Replace a string

Let's say we're given the following response that includes some ids:

{
"data":{
"ids":{
"id_1": "1identification_number=1anb4amca051",
"id_2": "22zlb4ftba492"
}
}
}

If we want to substitute "id=" for "identification_number=" in the first id, we can use SUBSTITUTE:

SUBSTITUTE(data.ids.id_1, "1identification_number=" , "id=")

This returns the following:

"id=1anb4amca051"

Example 2: Replace only select occurrences

If we want to use the optional arg4, which specifies the occurrence to substitute, we can do so using SUBSTITUTE .

In the following example, we only want to replace the second occurrence of a string and set it equal to "id=":

SUBSTITUTE(data.ids.id_2, "2", "id=", 1)

This results in the following:

"id=2zlb4ftba492"