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:
|
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:
|
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
Examples:
|
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:
|
Len(Arg1)
|
Returns the length of
Arg1 .
Arg1 is of type string and
the result is of type integer.
Examples:
|
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:
|
MostCommon(Arg1)
|
Returns the most common value of the
specified column. If several values are equally common, the first one will be
used.
Example:
|
NameDecode(Arg1)
|
Replaces all substring codes with decoded
characters.
Column names in Spotfire are stored as UTF-16 encoded strings. Example:
|
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 Spotfire are stored as UTF-16 encoded strings. Example:
|
Repeat
|
Repeats a string a specified number of
times.
Example:
|
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
Examples:
|
RXExtract(Arg1,
Arg2,
Arg3)
|
Returns the part of a string
( Examples:
|
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 .
"g" specifies that if
"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:
(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
For example, if a column called "X" containing the value
" Examples:
|
Substitute(Arg1,
Arg2,
Arg3)
|
Replaces all occurrences of
Arg2 in
Arg1 with
Arg3 . The search is case
sensitive.
Example:
|
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:
|
Trim(Arg1)
|
Removes whitespace characters from the
beginning and end of a string.
Example:
|
UniqueConcatenate(Arg1)
|
Concatenates the unique values converted to
strings. The values are ordered according the comparator.
Example:
|
Upper(Arg1)
|
Returns
Arg1 converted to uppercase.
Arg1 and the result are of
type string.
Example:
|