Text functions

The list shows the text functions that you can use in expressions.

Function	Description
`~=`	Can be part of an `If` or `Case` statement. Returns `true` if the `Arg2` regular expression string matches the `Arg1` string. Examples: `If( "aab" ~= "a+" , "true", "false" )` `Case when "aba" ~= ".a+$" then "true" else "false" end`
`Concatenate(Arg1, ...)`	Concatenates (appends) all the arguments into a string. If one argument is given, then the result is the concatenation of all rows. If more than one argument is given, then each row is concatenated. The arguments can be of any type, but are converted to strings. The result is of type string. Null arguments are ignored. Examples: `Concatenate("April ", 20+1, "st")` → `"April 21st"` `Concatenate(null, "Ape")`→ `"Ape"` `Concatenate (null, null)` → (Empty)
`Find(Arg1, Arg2, Arg3)`	Returns the index of the occurrence of the string `Arg1` in `Arg2`. `Arg3` optionally indicates which match to return. If no third argument is given then the first match is returned. If no match is found, 0 is returned. The search is case-sensitive. The first two arguments are of type string and the third argument and the result are of type integer. If `Arg1` is the empty string, 0 is returned. Examples: `Find("lo", "Hello")` → 4 `Find("a", "Hello")` → 0 `Find("", "Hello")` → 0 `Find("", null)` → (Empty) `If(Find("Pri 1", [Col1])>0, "Important", "Not important")` `Find("a", "ababab")` → 1 `Find("a", "ababab", 2)` → 3 `Find("a", "ababab", 10)` → 0
`Left(Arg1, Arg2)`	Returns the first `Arg2` characters of the string `Arg1`. `Arg1` and the result are of type string. `Arg2` is of type real, but only the integer part is used. If `Arg2` `>` the length of `Arg1`, the whole string is returned. If `Arg2` is negative, then the `Arg2` number of characters will be removed from the right-hand side of the `Arg1` string. Examples: `Left("Daddy", 1)` → `"D"` `Left("Daddy", 3.99)` → `"Dad"` `Left("Daddy", 386)` → `"Daddy"` `Left("Daddy", -1)` → `"Dadd"`
`Len(Arg1)`	Returns the length of `Arg1`. `Arg1` is of type string and the result is of type integer. Examples: `Len("Hello")` → 5 `Len(null)` → (Empty)
`Lower(Arg1)`	Returns `Arg1` converted to lowercase. `Arg1` and the result are of type string.
`Mid(Arg1, Arg2, Arg3)`	Returns the substring of `Arg1` starting at index `Arg2` with a length of `Arg3` characters. `Arg1` and the result are of type string. `Arg2` and `Arg3` are of type real, but only the integer part is used. If `Arg2 > Len(Arg1)`, an empty string is returned. Else, if `Arg2+Arg3 > Len(Arg1)`, `Arg3` is adjusted to `1+Len(Arg1)-Arg2`. If either of `Arg2` or `Arg3` is negative or if `Arg2` is zero, an error is returned. Examples: `Mid("Daddy", 2, 3)` → `"add"` `Mid("Daddy", 386, 4)` → `""` `Mid("Daddy", 4, 386)` → `"dy"` `Mid("Daddy", -1, 2)` → (Empty) `Mid("Daddy", 2, -1)` → (Empty)
`MostCommon(Arg1)`	Returns the most common value of the specified column. If several values are equally common, the first one will be used. Example: `MostCommon([Column])`
`NameDecode(Arg1)`	Replaces all substring codes with decoded characters. Column names in TIBCO Spotfire® are stored as UTF-16 encoded strings. Example: `NameDecode("Column %02D")`
`NameEncode(Arg1)`	Encodes characters in the string so that the string only contains characters matching the regular expression `[.0-9a-zA-Z]`. Column names in TIBCO Spotfire® are stored as UTF-16 encoded strings. Example: `NameEncode("Column £")`
`Repeat`	Repeats a string a specified number of times. Example: `Repeat("Hello", 2)` → `"HelloHello"`
`Right(Arg1, Arg2)`	Returns the last `Arg2` characters of the string `Arg1`. `Arg1` and the result are of type string. `Arg2` is of type real, but only the integer part is used. If `Arg2 >` the length of `Arg1`, the whole string is returned. If `Arg2` is negative, then the `Arg2` number of characters will be removed from the left-hand side of the `Arg1` string. Examples: `Right("Daddy", 1)` → `"y"` `Right("Daddy", 3.99)` → `"ddy"` `Right("Daddy", 386)` → `"Daddy"` `Right("Daddy", -1)` → `"addy"`
`RXExtract(Arg1, Arg2, Arg3)`	Returns the part of a string (`Arg1`) that matches a regular expression (`Arg2`). `Arg3` determines which match to use when there are multiple matches. Examples: `RXExtract([Column], "l+", 1)` `RXExtract("Parallel", "l+", 1)` → `"ll"` `RXExtract("Parallel", "l+", 2)` → `"l"`
`RXReplace(Arg1, Arg2, Arg3, Arg4)`	Replaces a substring according to a regular expression. Search for the `Arg2` regular expression in `Arg1` and replace it with `Arg3`. `Arg4` specifies the options for the replacement: "g" specifies that if `Arg2` matches more than once then all matches should be substituted. "i" specifies that the comparison should be case insensitive. "s", for single-line mode, specifies that the dot (.) matches every character (instead of every character except newline). Some characters, like for instance the backslash character "\", and parentheses, "(" and ")", need to be escaped to work when using calculated columns. Because parenthesis are used for grouping in regular expressions, both "(" and ")" need to be escaped if the ( or ) sign should be matched, and when the character is used in a regular expression double backslashes are needed in order to escape both the Spotfire string and the regular expression. See literature about regular expression language elements, e.g., on MSDN, for more information. Examples: `RXReplace("Hello", "L+", "LL", "i")` → `"HeLLo"` `RXReplace("3 Minor", "(\\d).*", "$1", "")` → 3 `RXReplace([Column with values within parenteses], "\$89\$", "", "")` `RXReplace("change\\slashdirection","\\\\","/","")` → `change/slashdirection` (In the last example, the backslash needs to be escaped twice; once for the Spotfire string and once for the regular expression.)
`Split(Arg1, Arg2, Arg3)`	Splits the values in `Arg1` into a number of substrings, using `Arg2` as the separator. `Arg3`specifies which of the values should be returned as a new column. Only one column is returned. Use a negative `Arg3` to specify that the split should be done from the end of the string toward the beginning, instead of from the beginning toward the end. For example, if a column called "X" containing the value "`a.b`" is split into two parts using the separator "`.`", then `Split([X],".",1)` will return "`a`" and `Split([X],".",2)` will return "`b`". Examples: `Split([Column]," ",2)` `Split([Column]," ",-2)` `Split([Column]," / ",3)`
`Substitute(Arg1, Arg2, Arg3)`	Replaces all occurrences of `Arg2` in `Arg1` with `Arg3`. The search is case sensitive. Example: `Substitute("Test","t","ting")` → `"Testing"`
`Substring(Arg1, Arg2, Arg3)`	Returns the substring of `Arg1` starting at index `Arg2` with a length of `Arg3` characters. `Arg1` and the result are of type string. `Arg2` and `Arg3` are of type real, but only the integer part is used. If `Arg2 > Len(Arg1)`, an empty string is returned. Else, if `Arg2 + Arg3 > Len(Arg1)`, `Arg3` is adjusted to `1+Len(Arg1) - Arg2`. If either of `Arg2` or `Arg3` is negative or if `Arg2` is zero, an error is returned. Examples: `Substring("Daddy", 2, 3)` → `"add"` `Substring("Daddy", 386, 4)` → `""` `Substring("Daddy", 4, 386)` → `"dy"` `Substring("Daddy", -1, 2)` → (Empty) `Substring("Daddy", 2, -1)` → (Empty)
`Trim(Arg1)`	Removes whitespace characters from the beginning and end of a string. Example: `Trim(" Example "`) → `"Example"`
`UniqueConcatenate(Arg1)`	Concatenates the unique values converted to strings. The values are ordered according the comparator. Example: `UniqueConcatenate([Column])`
`Upper(Arg1)`	Returns `Arg1` converted to uppercase. `Arg1` and the result are of type string. Example: `Upper("hello")` → `"HELLO"`

Contents

Index

Search Results

Text functions