# String functions

You can use the following string functions in calculated columns to manipulate, format, and analyze text-based values within Insights dashboards.

ALIASES

You can search for a function using its alias.

For example, if the function starts_with has the alias startswith:

  • When you type startswi, Insights suggests the starts_with function.
  • When you type startswith, Insights rewrites it as starts_with.

# contains

Returns TRUE if string contains the search_key and FALSE if otherwise.

Returns <null> if either input is null.

  • Return type: BOOLEAN
contains(string, search_key, case_sensitive)

# Parameters

Parameter Type Description
string VARCHAR The string to search in.
search_key VARCHAR The substring to search for.
case_sensitive BOOLEAN Optional. Determines whether the search is case-sensitive. Defaults to true.

# Example

This example filters records where product descriptions contain the organic keyword, ignoring case:

= contains(icon Description , "organic", false)

You can use this function to segment sales data by product attributes.

# starts_with

Returns TRUE if string starts with the start_key and FALSE if otherwise.

Returns <null> if either input is null.

  • Return type: BOOLEAN
  • Alias: startswith
starts_with(string, start_key, case_sensitive)

# Parameters

Parameter Type Description
string VARCHAR The string to check.
start_key VARCHAR The starting substring to check with.
case_sensitive BOOLEAN Optional. Determines whether the search is case-sensitive. Defaults to true.

# Example

This example filters projects for a specific department based on the project name, which starts with the department code.

For example, consider the project name [P&E] Test Automation, where [P&E] is the department code:

= starts_with(icon Project name , "[P&E]")

# ends_with

Returns TRUE if string ends with the end_key and FALSE if otherwise.

Returns <null> if either input is null.

  • Return type: BOOLEAN
  • Alias: endswith
ends_with(string, end_key, case_sensitive)

# Parameters

Parameter Type Description
string VARCHAR The string to check.
end_key VARCHAR The ending substring to check with.
case_sensitive BOOLEAN Optional. Determines whether the search is case-sensitive. Defaults to true.

# Example

This example retrieves transactions where payment references end with USD:

= ends_with(icon Payment reference , "USD")

You can use this function for use cases that require currency-specific financial reporting.

# concat

Returns a concatenation of all the string inputs.

  • Return type: VARCHAR
concat(string_1, .., string_n)

# Parameters

Parameter Type Description
string_1, .., string_n VARCHAR One or more strings to be concatenated.

# Example

This example combines first and last names into a single column containing the full name:

= concat(icon First name , " ",icon Last name )

You can use this function to create employee dashboards.

# substring

Returns a substring from string of substring_length starting at start_index.

If no substring_length is provided, the rest of the string from the start_index is returned.

  • Return type: VARCHAR
  • Alias: substr
substring(string, start_index, substring_length)

# Parameters

Parameter Type Description
string VARCHAR The string to extract from.
start_index BIGINT The starting position of the substring to extract (1-based index).
substring_length BIGINT Optional. The length of the substring to extract. If missing or greater than the length of the original string, the returned substring will run to the end of the original string.

# Example

This example extracts the product category from a product code, where the product category is represented by the first four characters of the product code:

= substring(icon Product code , 1, 4)

You can use this function to visualize product sales for different categories.

# trim

Removes any leading and/or trailing characters that matches the key from string.

  • Return type: VARCHAR
trim(specification, key, string)

# Parameters

Parameter Type Description
specification VARCHAR Optional. Specifies which part of the string to trim. Allowed values include "leading", "trailing", and "both". Defaults to "both".
key VARCHAR Optional. The character to remove from the string. Defaults to " " (space).
string VARCHAR The string to trim.

# Example

This example removes leading dollar signs from revenue values:

= trim("leading", "$",icon Revenue )

You can use this function to create clearer visualizations in a financial dashboard.

# replace

Replaces all instances of search_key with replacement in string.

  • Return type: VARCHAR
replace(string, search_key, replacement)

# Parameters

Parameter Type Description
string VARCHAR The string to search in and modify.
search_key VARCHAR The substring to search for and replace.
replacement VARCHAR The substring to insert into the original string.

# Example

This example replaces underscores in product names with spaces:

= replace(icon Product name , "_", " ")

You can use this function to enhance readability in product performance dashboards.

# lower

Converts string to lowercase.

  • Return type: VARCHAR
lower(string)

# Parameters

Parameter Type Description
string VARCHAR The input string to covert to lowercase.

# Example

This example converts region names to lowercase:

= lower(icon Region name )

You can use this function to create accurate grouping and consistent labels in a dashboard.

# upper

Converts string to uppercase.

  • Return type: VARCHAR
upper(string)

# Parameters

Parameter Type Description
string VARCHAR The input string to convert to uppercase.

# Example

This example converts region names to uppercase:

= upper(icon Region name )

You can use this function to create accurate grouping and consistent labels in a dashboard.

# titleize

Returns the input string with the first letter of each word in uppercase and the subsequent letters in lowercase.

  • Return type: VARCHAR
  • Alias: initcap(string)
titleize(string)

# Parameters

Parameter Type Description
string VARCHAR The input string to titleize.

# Example

This example formats product names in title case:

= titleize(icon Product name )

You can use this function to create a polished presentation in sales dashboards.

# left

Returns the leftmost substring of the specified length.

  • Return type: VARCHAR
left(string, substring_length)

# Parameters

Parameter Type Description
string VARCHAR The string to extract from.
substring_length BIGINT The number of characters to return from the left side of the string.

# Example

This example extracts the product category from a product code, where the product category is represented by the first four characters of the product code:

= left(icon Product code , 4)

You can use this function to visualize product sales for different categories.

Returns the rightmost substring of the specified length.

  • Return type: VARCHAR
right(string, substring_length)

# Parameters

Parameter Type Description
string VARCHAR The string to extract from.
substring_length BIGINT The number of characters to return from the right side of the string.

# Example

This example extracts the last six characters of transaction IDs, which represent the transaction date:

= right(icon Transaction ID , 6)

For example, if the icon Transaction ID is XJBDJF120424, this function returns 120424.

You can use this function to analyze transaction volume over time.


Last updated: 3/14/2025, 4:57:44 PM