Tableau – String Functions

Tableau provides several string functions that are used to manipulate character type data. These functions allow users to perform various operations on strings, such as converting to upper or lower case, finding specific substrings, trimming, and more.

Here are some of the most commonly used string functions in Tableau:

LOWER:

This function returns the given upper case string in the lower case. 

Syntax:

LOWER(string)

Example:

LOWER(TABLEAU) = tableau

UPPER:

 This function returns the given lower case string in the upper case.

Syntax:

UPPER(string)

Example:

UPPER(tableau) = TABLEAU

ASCII: 

This function returns the ASCII code for the first character in the string. 

Syntax: 

ASCII(String Value)

Example: 

ASCII(‘A’) = 65

CHAR: 

This function returns the character when an ASCII value is passed.

Syntax:

CHAR(ASCII Value)

Example:

CHAR(65) = ‘A’

CONTAINS:

This function is used to find if a certain substring is present in the string.

Syntax:

CONTAINS(String Value).

Example: 

CONTAINS(TABLEAUTUTORIAL, TUTORIAL)

ENDSWITH:

This function returns a true if the given string ends with the specified substring. ENDSWITH ignores trailing white spaces.

Syntax: 

ENDSWITH(string, substring)

Example:

ENDSWITH(“TABLEAUTUTORIAL”, “RIAL”) = true

FIND: 

This function returns an index position of the substring in a string or null if it is not found. If another argument start is added, the function ignores any substring instances before the index position starts. The beginning character in the string is at index 1.

Syntax:

FIND(string, substring, [start])

Example: 

FIND("ABCD", "CD") = 2

LEFT:

This function returns the left-most number of characters in the string. 

Syntax:

LEFT(String, number).

Example:

LEFT("TABLEAUTUTORIAL", 5) = "TABLEAU"

RIGHT:

This function returns the right-most number of characters in the string. 

Syntax:

RIGHT(String, number)

Example:

RIGHT("TABLEAUTUTORIAL", ) = "TUTORIAL"

LEN: 

This function returns the length of the given string. 

Syntax:

LEN(String Value)

Example:

LEN(“TABLEAUTUTORIAL”) = 15

TRIM: 

This function returns the string with leading and trailing spaces removed.

Syntax:

TRIM(String)

Example:

TRIM(" TABLEAUTUTORIAL ") = "TABLEAUTUTORIAL"

LTRIM:

This function returns the string after removing any leading spaces.

Syntax:

LTRIM(String)

Example: 

LTRIM(“ TABLEAUTUTORIAL ”) = “TABLEAUTUTORIAL”

MAX:

This function returns the maximum of a and b. 

Syntax: 

MAX(a,b)

Example:

MAX ("abc","pqrs") = "pqrs"

MID: 

This function returns the string starting at the starting position. The initial character in the string is position 1. If another argument length is included, the returned string includes only that

SUBSTITUTE:

The SUBSTITUTE function is used to replace all occurrences of a given substring in a string with a new substring.

Syntax:

SUBSTITUTE(string, oldSubstring, newSubstring)

Example:

SUBSTITUTE("Hello, World!", "o", "i") = "Helli, Wirlld!"

REPEAT:

The REPEAT function is used to repeat a given string a specified number of times. 

Syntax:

REPEAT(string, numTimes)

Example:

REPEAT("abc", 3) = "abcabcabc"

REGEXP_EXTRACT:

The REGEXP_EXTRACT function is used to extract a substring from a string based on a regular expression pattern.

Syntax:

REGEXP_EXTRACT(string, pattern)

Example:

REGEXP_EXTRACT("abc123def456", "[0-9]+") = "123"

REGEXP_REPLACE

The REGEXP_REPLACE function is used to replace parts of a string that match a regular expression pattern with a new substring. 

Syntax:

REGEXP_REPLACE(string, pattern, replacement)

Example:

REGEXP_REPLACE("abc123def456", "[0-9]+", "###") = "abc###def###"

CONCAT:

The CONCAT function is used to concatenate two or more strings into a single string. 

Syntax:

CONCAT(string1, string2, ...)

Example:

CONCAT("Hello", ", ", "World!") = "Hello, World!"

FORMAT

The FORMAT function is used to format a number or date value as a string. 

Syntax:

FORMAT(value, formatString)

Example:

FORMAT(12345.6789, "$#,##0.00") = "$12,345.68"

These are just a few of the many String Functions available in Tableau. By mastering these functions, you can perform a wide range of data manipulation tasks on your string data.