REPLACE

This function allows you to easily replace values within a chosen field.

REPLACE( <Field Name or Expression>, “<String to Find>”, “<String to Replace>” [, <Replace Option>] )

  • Field Name or Expression: The Field Name or Expression to Apply this change to

  • String to Find: The String which will be replaced

  • String to Replace: The String which will be overwriting the previous data

  • Replace Option: Determines which data will be replaced:

    • 0 – Replace All occurrences of this within a string

    • 1 – Replaces only the first occurrence of this within a string

    • 2 – Replaces only the last occurrence of this within a string

    • If left empty, will default to 0 – Replace All.

Example: create a field which removes all “B” characters present in the Product Code

REPLACE( [Transactions.ProductCode] , “B”, “” )

Result:

  • Returns 2014-ACD-2014 for an original product code of 2014-ABCD-2014
  • Returns 2014-C-2014 for an original product code of 2014-CBBB-2014

Example: create a field that updates all product codes which end “-2014” to end instead with “_Expired”.

REPLACE( [Transactions.ProductCode] , “-2014”, “_Expired”, 2 )

Result:

  • Returns 2014-AAAA_Expired for an original product code of 2014-AAAA-2014
  • Returns 2016-ABAB-2016 for an original product code of 2016-ABAB-2016 (no change)