Substitute String (Expression Function)

Purpose

The Substitute String function (SUBSTITUTE_STRING) is used to substitute one value for another in a string of text. Most often used to clean up strings of text that may contain invalid characters.

Parameters

The Substitute String function has five parameters, two of which are required:

Allowed Inputs: field value or text string

The first required parameter is the string that is being searched. Either use the use the Query Builder to choose a field that contains the search value or use type a value to search for a text string.

String to Find

Allowed Inputs: field value or text string

The second required parameter of the Substitute function is the string that we are looking for. It can either be obtained from a field using the Query Builder or from a literal string.

Replacement Value

Allowed Inputs: field value or a text string

The third parameter, which is optional, it is a value that will replace the value that was found. If this option is blank or set to any empty string,“”, then the values that are found will be removed from the string.

Instance

Allowed Inputs: field value, a positive number, or 0 to ignore this parameter.

The fourth parameter, which is optional, is a number denoting which occurrence of the search value to replace. If this option is not used then the all occurrences are replaced.

Case Sensitive

New as of Platform Version 15.11.1195

Allowed Inputs: field value, Yes or No.

The fifth parameter, which is optional, tells the function specifically whether to be case sensitive or not. The default is 'Yes', case sensitive.

Output

The output of the Substitute function is a string with the value in the string to find parameter removed or replaced with the replacement value.

Example

A common use for this function is to replace or remove unwanted or invalid values from a string. For example SUBSTITUTE_STRING(“51%”,“%”,“ percent”) would result in “51 percent”.

expression function - substitute.txt · Last modified: 2016/09/14 14:19 (external edit)
Copyright WorkXpress, 2022