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.
SUBSTITUTE(arg1, arg2, arg3, arg4)
Arguments | Description |
| String or block of text to be searched. |
| Search string. |
| String with which to replace the search string. |
| Occurrence number. |
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, use the following function.
# Substitute one string for anotherSUBSTITUTE(data.ids.id_1, "1identification_number=" , "id=")​# Returns "id=1anb4amca051"
Here is the resultant JSON.
{"data":{"ids":{"id_1": "id=1anb4amca051","id_2": "22zlb4ftba492"}}}
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 stringSUBSTITUTE(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"}}}