SUBSTITUTE

SUBSTITUTE replaces a segment of a string with another string.

Function category: Text​

Syntax

SUBSTITUTE(arg1, arg2, arg3, arg4)

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"
      }
   }
}