Locate_In_String - Determine the last occurence of a character or a set of characters

gistfile1.txt

-- With the scalar function LOCATE_IN_STRING it is possible to search a string backwards
-- IBM Documentation: If the value of the 3rd parameter (START) is less than zero, the search begins at
--                    CHARACTER_LENGTH(source-string) + start + 1 and continues for each position to the 
--                    beginning of the string

-- Example, determine the last '/' in an IFS file name, so the file name can be determined
With x (IFSFile) as (Values('/home/Hauser/JSON/Examples/MyFile.json'),
                           ('/MyCompany/MyFirstDirectory/My2ndDir/LastDir/Sales20191103.txt'),
                           ('Dir01/NextDir/MySQLScript.sql'),
                           ('MyFile.txt'),
                           ('/home/Dir01/Dir02/'))
Select IFSFile, Locate_In_String(IFSFile, '/', -1),
       Substr(IFSFile, Locate_In_String(IFSFile, '/', -1) + 1)
  from x;