Text Functions


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)

Returns the 1-based index of the first occurrence of the string Arg1 in Arg2. If not found, 0 is returned. The search is case-sensitive. The arguments are of type string and the result is 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")

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, an error is returned.

 

Examples:
Left("Daddy", 3.99)     -> "Dad"
Left("Daddy", 386)      -> "Daddy"
Left("Daddy", -1)         -> (Empty)

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, while variable names in TIBCO Spotfire Statistics Services are built from 8-bit ASCII characters matching [.0-9a-zA-Z] or ASCII strings enclosed in grave accents. Thus, the column names that are sent to TIBCO Spotfire Statistics Services must be encoded. Column names received from TIBCO Spotfire Statistics Services are automatically decoded by the built-in data functions output handlers. This function can be used to decode results that have not been automatically decoded.

 

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, while variable names in TIBCO Spotfire Statistics Services are built from 8-bit ASCII characters matching [.0-9a-zA-Z]. Thus, the column names that are sent to TIBCO Spotfire Statistics Services must be encoded. This is done automatically when sending data to TIBCO Spotfire Statistics Services via the built-in data functions input handlers. If you need to provide column name input by some other means (e.g., via a document property) you may need to use this function to encode the column names before applying the data function.

 

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, an error is returned.

 

Examples:
Right("Daddy", 3.99)     -> "ddy"
Right("Daddy", 386)      -> "Daddy"
Right("Daddy", -1)         ->  (Empty)

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 "\", need to be escaped to work when using calculated columns. See literature about regular expression language elements, e.g., on MSDN, for more information.

 

Example:

RXReplace("Hello", "L+", "LL", "i")            -> "HeLLo"

RXReplace("3 Minor", "(\\d).*", "$1", "")    -> 3

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.)

Substitute(Arg1, Arg2, Arg3)

 

Replaces all occurrences of Arg2 in Arg1 with Arg3. The search is case sensitive.

 

Example:

Substitute("Test","t","ting")                -> "Testing"

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"

See also:

Binning functions

Conversion functions

Date and Time functions

Logical functions

Math functions

OVER functions

Property functions

Ranking functions

Spatial functions

Statistical functions