Today we were given an overview of many of Tableau’s functions, one of which was the String Function FINDNTH. It allows you to find the position of a particular letter or sub-string within a string.
• Continue reading to learn about how to use it.
• Scroll down to find out why it might be useful.
Here’s a simple example, if you wanted to find the position of the first letter ‘o’ in the sentence ‘The cow jumped over the moon’ you could count each character (including spaces) and see that it is the 6th character.
What happens when you want to find the position of the last ‘o’ in the string? One option would be to count the number of ‘o’s – there are four – then look for the position of the 4th ‘o’.
A better solution is to use -1. When you use negative numbers, the function will look at the string in reverse order, so it finds the first ‘o’ reading from right to left, aka: the last ‘o’.
The results of these calculations are displayed below. You’ll notice that the second two calculations give the exact same result – the 4th and last ‘o’ is the 27th character in the string.
Why might you ever need to use this function?
FINDNTH( ) will probably be most useful in combination with other String functions. A practical example might be if you wanted to split up an ID Field or just take the first part of a product code that may be split up by hyphens. The following example is less practical, but it illustrates what is happening with the calculation and shows how it can make functions like LEFT/RIGHT more dynamic.
The results of this calculation look like this:
