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])
