SUBSTITUTE

The SUBSTITUTE Function

Function Group: Text

SUBSTITUTE replaces a segment of a string with another string.

Syntax

SUBSTITUTE(arg1, arg2, arg3, arg4)

• arg1 is a string or block of text to be searched
• arg2 is the search string
• arg3 is the string to replace the search string with
• arg4 is occurrence number

Uses

Let's say we're given a response with 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 will return "id=1anb4amca051"

Other Examples

If we want to use the optional fourth argument, which specifies the occurrence to substitute, we can do so. In the case below we only want to remove the first 2 and set it equal to "id=":

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

Notes

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