SUBSTITUTE

Replaces existing text with new text in a string.

Parameters

text String (required) - A text value to look in

search_for String (required) - A text value to replace

replace_with String (required) - A text value to replace it with

occurrence String (required) - The instance of the search text to replace (e.g. 4 will only replace the 4th instance)

Returns

String - the new text string

Examples

// replaces all occurrences
SUBSTITUTE('The data is stored in a database', 'data', 'record data')

// returns "The record data is stored in a record database"

// replaces the first occurrence
SUBSTITUTE('The data is stored in a database', 'data', 'record data', 1)

// returns "The record data is stored in a database"

// replaces the 2nd occurrence
SUBSTITUTE('The data is stored in a database', 'data', 'record data', 2)

// returns "The data is stored in a record database"

SUBSTITUTE('The data is stored in a database', 'a', 'A')

// returns "The dAtA is stored in A dAtAbAse"

Updated about 1 year ago