Let’s pull the strings…this time in tableau. -By Mohini Tilekar

Image by Author

In this blog, I am going to describe the string functions provided by Tableau. String functions are the type of calculations used to manipulate string data. While working on string data, we will come across situations where we want to trim spaces, find substrings, count no of characters in the string, and so on. In such situations, these string functions will be imperatives.

You can make use of the "Calculated Field" option from the drop-down menu near the search bar to access all these string functions.

When you click on the Create calculated field option from the drop-down menu, a small window will appear on the screen with all the in-built functions available in Tableau. From the list, you can select the function you want. You can also see the brief description for the selected function on the right side.

Image by Author

String Functions in Tableau

1. ASCII:

This function returns the ASCII code of the first character of the given string.

Syntax: ASCII(Number)

e.g.: ASCII(‘Mohini’) = 77

2. CHAR:

This function returns the character represented by the ASCII code which is passed to the function.

Syntax: CHAR (ASCII Code)

e.g.: CHAR (88) = ‘X’

3. CONTAINS:

This function checks if the given substring is present in the given string. If substring is present function returns True otherwise it returns False.

Remember that its case sensitive.

Syntax: CONTAINS (string, substring)

e.g.: CONTAINS (‘Hi there’, ‘Hi’) = true

4. ENDSWITH:

This function returns true if the given string is ends with given substring.

Remember that white spaces are matched too.

Syntax: ENDSWITH (string, substring)

e.g.: ENDSWITH (‘Tabalue’, ‘lue’) = true

5. FIND:

This function returns index position of the given substring from the given string. It returns 0 if substring not found. If the function is provided with the optional 3rd parameter start then function ignores any occurrences of the given substring before the specified index position in start.

Syntax: FIND (string, substring, [start])

e.g.: FIND (string, substring, [start])

FIND('occurrences','c',4) = 9

FIND ('occurrences’,'c') = 2

6. FINDNTH:

This function returns index position of the nth occurrence of the given substring within the given string. Where n is the 3rd argument passed to the function.

Syntax: FINDNTH (string, substring, occurrence)

e.g.: FINDNTH: ('occurrences','c',2) = 3

FINDNTH: ('occurrences’,'c',4) = 0

7. LEFT:

This function returns the N leftmost characters from the given string. N is the 2nd parameter passed to the function.

Syntax: LEFT (string, number)

e.g.: LEFT('occurrences',2) = ‘oc’

LEFT('occurrences',10) = ‘occurrence’

8. LEN:

This function returns length of the given string.

Syntax: LEN (string)

e.g.: LEN('occurrences') = 11

9. LOWER:

This function returns length of the given string.

Syntax: LOWER (string)

e.g.: LOWER('TABLEAU') = ‘tableau’

10. LTRIM:

This function returns the string with any leading white spaces removed of the given string.

Syntax: LTRIM (string)

e.g.: LTRIM (' TABLEAU') = ‘TABLEAU'

11. MID:

This function returns the sub string from main string from the index position given as argument. You can also specify the length of substring you want as the third argument in function.

Syntax: MID (string, start, [length])

e.g.: MID ('consclusion',3) = ‘nsclusion’

MID ('consclusion',5,3) = ‘clu’

12. REPLACE:

This function returns the sub string from main string from the index position given as argument. You can also specify the length of substring you want as the third argument in function.

Syntax: REPLACE (string, substring, replacement)

e.g.: REPLACE('conclusion','clu','zzz') = 'conczzzsion'

13. RIGHT:

This function returns the N rightmost characters from the given string. N is the 2nd parameter passed to the function.

Syntax: RIGHT (string, number)

e.g.: RIGHT ('occurrences',2) = ‘es’

RIGHT ('occurrences',10) = ‘ccurrences’

14. RTRIM:

This function returns the string with any succeeding white spaces removed from the given string.

Syntax: RTRIM (string)

e.g.: RTRIM ('TABLEAU ') = ‘TABLEAU'

15. SPACE:

This function returns the string containing specified no of spaces.

Syntax: SPACE (number)

e.g.: SPACE (3) = ‘ '

16. SPLIT:

This function returns the substring of the given string separated by delimiter character as a sequence of token.

Syntax: SPLIT (string, delimiter, token number)

e.g.:SPLIT('a-b-c-d-e-f','-',5) = ‘e’

17. STARTSWITH:

This function returns true if the given string is ends with given substring.

Remember that white spaces are matched too. Remember that it is case sensitive.

Syntax: STARTSWITH (string, substring)

e.g.: STARTSWITH (‘Tabalue’, ‘Tab’) = true

18. TRIM:

This function returns the string without any preceding or succeeding white spaces from the given string.

Syntax: TRIM (string)

e.g.: TRIM (' TABLEAU ') = ‘TABLEAU'

19. UPPER:

This function returns length of the given string.

Syntax: UPPER (string)

e.g.: UPPER(‘tableau’) = ‘TABLEAU'