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

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

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

Example 1: Replace a string

If we want to substitute "id=" for "identification_number=" in the first id, use the following function.

# Substitute one string for another
SUBSTITUTE(data.ids.id_1, "1identification_number=" , "id=")
# Returns "id=1anb4amca051"

Here is the resultant JSON.

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

Example 2: Replace only specific occurrences

After running the function in example 1, we can make changes to the value of "id_2". We can replace the second occurrence of a string and set it equal to "id=" use the optional arg4 . This specifies the occurrence to substitute, as in following function.

# Substitute only selected occurance of a string
SUBSTITUTE(data.ids.id_2, "2", "id=", 1)
# Returns "id=2zlb4ftba492"

Here is the resultant JSON.

{
"data":{
"ids":{
"id_1": "id=1anb4amca051",
"id_2": "id=2zlb4ftba492"
}
}
}