Last occurrence of sub-string in a string - The locate function secret part in Oracle Analytics

 I need to find the last occurrence of a specific character in a string (and using only the part that follows that character). For example in a string 'abc-de-fghi-jklm' I want to locate the last '-' and get the sub-string of 'jklm'. In my cases there is unknown number of '-' characters.

The basic function to locate a sub-string in a string is "Locate". 

While it seems Locate has only 2 parameters (the string we are searching and the full string), actually they are 3 parameters. The third one is optional: start position.

LOCATE(CharacterExpression, CharacterExpression [, StartPosition] ) 

The Start Position can accept negative values as well, meaning search from the end of the string.

So in my case Locate('-', 'abc-de-fghi-jklm', -1) is the solution.

To get the sub-string 'jklm' I will use 

SUBSTRING( 'abc-de-fghi-jklm'

                   FROM  Locate('-', 'abc-de-fghi-jklm', -1)+1)

The SUBSTRING function parameters are:

SUBSTRING(CharacterExpression FROM StartPosition [FOR length])