Amazon QuickSight – Substring

The ‘substring’ function in Amazon QuickSight is used to extract a portion of a string expression. It returns a new string that consists of a specified number of characters starting at a specified position in the original string.

Syntax

#Start#
substring(expression, start, length)
#End#

This function takes three arguments:

• expression (required): The string to extract a substring from. This can be a field, column, or expression that returns a string value.

• start (required): The starting position of the substring. This parameter specifies the index of the first character to include in the substring. The index is zero-based, so the first character in the string has an index of 0.

• length (optional): The length of the substring. This parameter specifies the number of characters to include in the substring. If this parameter is omitted, the function returns all the characters from the starting position to the end of the string.

Suppose you have a dataset containing a field with product codes, and you want to create a calculated field that extracts the first three characters from each code.

Assume the following dataset:

To create a calculated field that extracts the first three characters from each code, you can use the substring function:

• Click on the “Add calculated field” button in the analysis pane.
• Enter a name for the calculated field, such as “Product Code Prefix”.
• Enter the following expression for the calculated field:

Example

#Start#
substring({Product Code}, 0, 3)
#End#

This expression extracts the first three characters from the Product Code field in the current row.

• Click “Create” to create the calculated field.

After creating the calculated field, the dataset would include the following additional column:

The substring function is useful for extracting specific portions of a string, such as prefixes or suffixes, for various data analysis purposes.