Tableau has many inbuilt string functions which can be used to do string manipulations like – comparing, concatenating, replacing few characters from a string etc.
Step 1: To explore and create the logical functions, use the Calculated Fields. To bring up the Calculated Field window, right-click anywhere in the Data window i.e. sidebar to bring up that menu. Select Create calculated field…
Step 2: The above step opens a calculation editor which lists all the functions that is available in Tableau. If you right-clicked on a particular dimension or measure to create your calculation, then it will appear in the Calculated Field window in the formula text area. You can change the drop-down value and see only the String functions.
Step 3: After selecting the Date function, it lists all the functions available under it’s category. On selecting each function, it describes about what the function does with an example so it would be easy to use the same.
Step 4: Now lets have a brief knowledge about the available String functions and their application.
1. ASCII Function :
The ASCII function returns the ASCII code for the first character in the string.
Example : ASCII(“Aurora”) = 65
2. CHAR Function :
The CHAR function returns the character encoded by the ASCII code number. This function works in the reverse of the ASCII function.
Example : CHAR(65) = ‘A’
3. CONTAINS Function :
CONTAINS function will return a true if the given string contains the specified substring.
Example : CONTAINS(“Visualization”, “sual”) = true
4. ENDSWITH Function :
This function returns true if the given string ends with the specified substring otherwise false.
Example : ENDSWITH(“Computer”, “ter”) = true
5. FIND Function :
FIND(string, substring, [start])
The FIND function returns the start of the substring within the string or 0 if the substring is not found. The first character in the string is position 1.
Example : FIND(“Panorama”, “ora”) = 4
If the optional argument start is added, the function ignores any instances of substring that appear before the index position of start.
Example : FIND(“Panorama”, “a”, 4) = 6
6. FINDNTH Function :
FINDNTH(string, substring, occurrence)
Returns the position of the nth occurrence of substring within the specified string, where n is defined by the occurrence argument.
Example : FIND(“Panorama”, “a”, 2) = 6
7. ISDATE Function :
This function tests a string to determine if it is a valid date. Returns true when string otherwise false.
Example : ISDATE(“December 12, 2012”) = true
8. LEFT Function :
Returns the left-most characters of the string using the specified number as the amount.
Example : LEFT(“Information”, 4) = “info”
9. LEN Function :
The LEN function returns the character count or the length of the given string.
Example : LEN(“Answer”) = 6
10. LOWER Function :
This function returns all characters of the given string into lower case letters.
Example : LOWER(“Answer”) = “answer”
11. LTRIM Function :
This function will remove any spaces starting the string.
Example : LTRIM(” Tableau”) = “Tableau”
12. MAX Function :
MAX(expression) or MAX(expr1, expr2)
MAX finds the value that is highest in the sort sequence defined by the database for that column. It returns Null if either argument is Null.
Example : MAX(“Compute”, “Evaluate”) = “Evaluate”
13. MID Function :
MID(string, start, [length])
The MID function returns the characters from the middle of a text string. The start argument is where the returned value will begin and the length argument is how many characters will be returned. If the length is not included, then all remaining characters after the start position will be included. The first character in the string is position 1.
Example : MID(“December”, 3 , 4) = “cemb”
14. MIN Function :
MIN(expression) or MIN(expr1, expr2)
The MIN function will return the lower value based on the sort sequence as defined in the database. If either argument is NULL, then this function will return NULL.
Example : MIN(“August”, “December”) = “August”
15. REPLACE Function :
REPLACE(string, substring, replacement)
This function will search for the occurrence of the substring in the string and replace those characters with the replacement string. If the substring is not found in the string, then there is no change.
Example : REPLACE(“Calculation”, “ion”, “ed”) = “Calculated”
16. RIGHT Function :
It returns the characters from the end of a given string, the amount determined by the number of characters argument.
Example : RIGHT(“December”, 4) = “mber”
17. RTRIM Function :
The RTRIM function returns the string with any trailing spaces removed.
Example : RTRIM(“Tableau “) = “Tableau”
18. SPACE Function :
The SPACE function returns a string with the number of spaces defined by the number argument.
Example : SPACE(2) = ” ”
19. SPLIT Function :
SPLIT(string, delimiter, token number)
This function returns a substring from a string, using a delimiter character to divide the string into a sequence of tokens. The string is interpreted as an alternating sequence of delimiters and tokens. So for the string a-b-c-d, where the delimiter character is ‘-‘, the tokens are a, b, c and d. Think of these as tokens 1 through 4. SPLIT returns the token corresponding to the token number. When the token number is positive, tokens are counted starting from the left end of the string; when the token number is negative, tokens are counted starting from the right.
Example : SPLIT(‘a-b-c-d’, ‘-‘, 2) = ‘b’ Or SPLIT(‘a|b|c|d’, ‘|’, -2) = ‘c’
20. STARTSWITH Function :
This returns a true or false result if the string starts with the substring.
Example : STARTSWITH(“December”, “ber”) = true
21. TRIM Function :
The TRIM function removes any leading or trailing spaces from the string.
Example : TRIM(” Tableau “) = “Tableau”
22. UPPER Function :
The UPPER function takes all the characters in the string and converts them to uppercase characters.
Example : UPPER(“aurora”) = “AURORA”