This article contains the following sections:
Available text functions
Below are all available text functions in BIME. In the Functions window, you can view only text functions by clicking the Filter button, then selecting the Text category.
|CONTAINS(Text, Text_to_search)||Returns TRUE if the first entered parameter contains the second as a substring.|
|STARTSWITH(Text,Text_to_search)||Returns TRUE if the first entered parameter starts with the second.|
|ENDSWITH(Text, Text_to_search)||Returns TRUE if the first entered parameter ends with the second.|
|LEFTPART(Text,Number)||Returns the left-most number of characters in the entered text.|
|RIGHTPART(Text,Number)||Returns the right-most number of characters in the entered text.|
|SUBSTR(Text,Number_start_index,Number_end_index)||Returns a text consisting of the character specified by start index (the first entered integer) and all characters up to end index-1 (the second entered integer).|
|FIND(Text, Text_to_find, Number_start_index)||Returns the index, in the first entered text, of the first instance of the second entered text, from the entered start index. Returns -1 if the second entered text is not found. Note: The index of the first character of a substring is 0.|
|LENGTH(Text)||Returns the length of the entered text.|
|REPLACE(Text, Text_to_replace,Text_to_replace_with)||Returns a copy of the first entered text in which all instances of the second entered text have been replaced with the third.|
|LOWERCASE(Text)||Returns the lower-case version of the entered text.|
|UPPERCASE(Text)||Returns the upper-case version of the entered text.|
|LTRIM(Text)||Returns a copy of the entered text in which all left-white spaces have been removed.|
|RTRIM(Text)||Returns a copy of the entered text in which all right-white spaces have been removed.|
|TRIM(Text)||Returns a copy of the entered text in which all left and right-white spaces have been removed.|
|LPAD(Text,length,pad)||Pads text on the left and repeats until the resulting string is exactly the entered character length.|
|RPAD(Text,length, pad)||Pads text on the right and repeats until the resulting string is exactly the entered character length.|
|SENTIMENT_ANALYSIS(_text)||Returns 'Positive' or 'Negative' based on analysis of the text.|
|IS_POSITIVE(text)||Returns 'true' for positive and 'false' for negative based on analysis of the text.|
|STRING(number)||Converts the entered number to text.|
|REGEXP_MATCH(text, regexp)||Returns true if the entered text matches the entered regular expression.|
|REGEXP_EXTRACT(text, regexp)||Returns the portion of the entered text that matches the capturing group within the entered regular expression|
|REGEXP_REPLACE(text, regexp, replace text)||Returns a string where any substring of the entered text that matches the entered substring is replaced by the entered replacement text. For example, REGEXP_REPLACE("Hello","lo", "p"_ returns Help.|
Writing formulas with text functions
This section will provide an example of how you can use text functions in your reports. The example also uses the IF THEN ELSE function to create a conditional expression (see Using the IF THEN ELSE function).
Filtering values with text functions
The CONTAINS function provides an easy way to filter results by values containing a specific text. The example below uses CONTAINS to limit results to values in the Group attribute with "Support" included in the value label.
- In the Calculation engine menu, select Standard calculated attribute.
- Name your calculated attribute. This example uses Support groups.
- Click the +Add button under Functions.
- Select the IF THEN ELSE function.
- Click inside the _boolean_condition parentheses.
- Click the +Add button under Functions.
- Select the CONTAINS function.
- In the _text parameter, select your attribute containing the results you would like to filter by. This example uses Group.
- In _text_to_search, enter the text to use as your filter. All text must be entered in "double quotes". This example uses "Support".
- For the _value_if_true select your _text parameter attribute from the Select a field drop-down list.
- Delete ELSE and value_if_false.
- Click Save.
- Click the + button on Columns, Rows, or Explosions to add your calculated attribute to your query