Substitute replaces some or all of a string value returned by a measure with another string.



A list of comma delimited "pattern":"replacement" pairs. All occurrences of pattern in the measure string value are replaced with replacement. For example, "This":"That" substitutes all occurrences of This with That.

If multiple "pattern":"replacement" pairs are specified, each substitution is attempted in the specified order. For example, "This":"That","Here":"There" first replaces all occurrences of This with That and then replaces all occurrences of Here with There.

Each of the "pattern":"replacement" pairs are a separate action, and act on the results of the previous substitute pairs. For example, with "1":"One","10":"Ten", all occurrences of 1 are replaced with One, but occurrences of 10 will not be replaced with Ten, because the first substitution already changed all 1 characters to One. For the desired behavior, the order can be reversed as "10":"Ten","1":"One".

Instead of "pattern":"replacement", single quotes can be used either around the pattern or the replacement. (i.e. 'pattern':"replacement" or "pattern":'replacement', but not 'pattern':'replacement') This can be useful when either the pattern or the replacement contains double quotes. For example, '"':"double quote" replaces all occurrences of " with double quote.

RegExpSubstitute Default: 0

If set to 1, Perl compatible regular expressions can be used in the pattern part of Substitute pairs.

If captures are used in the pattern, (e.g. (.+)) they can be referenced in the replacement part using \1, (first capture) \2, (second capture) etc. The entire match can also be referenced with \0.

Note: With RegExpSubstitute, a (capture) may not return an empty string. Care should be taken when using * (zero or more) quantifiers or (?(?= (If/Then) lookahead tests, as a capture must either cause the entire regular expression to "fail", or return a value of some kind.


Normal substitution:

Substitute="2012":"Twenty Twelve","2013":"Twenty Thirteen"
; Assuming that the current year is 2012, the string value of [MeasureYear] will
; be "Twenty Twelve" (without quotes). Since Substitute only affects the string
; value, the number value of [MeasureYear] will continue to be 2012.

; Since the number value of MeasureYear is used above, the value of [MeasureCalc1]
; will also be 2012.

; Since the string value of MeasureYear is used above, a syntax error will occur
; (as "Formula=Twenty Twelve" is not a valid formula).

Regular expression substitution:

String=I am Rainy
Substitute="(\w+) (\w+) (\w+)":"\3, \1 \2","Rainy":"Yoda"
; Reorders the sentence and then replaces Rainy with Yoda.
; The result is: Yoda, I am

String=Hello, world!
; Truncates string by length (in this case 5) and appends "...".
; The result is: Hello...

; Masks an IP address. The result is: 192.168.1.***