Change folder path in field with SQL-PL

Question

I've got a field in a DB2 table which holds a file path. The value will look something like this:

N:\Folder1\Folder2\Folder3\Folder4\ITP201602

I need a stored procedure which will change the last folder with a time stamp, which will appear as ITPYYYYMM. The ITP is a placeholder value which will not change. It is required by the other software that looks at this path.

Here is my current query, which works:

   CREATE PROCEDURE UPDATE_ITP_DOCUMENT_PATH()
   LANGUAGE SQL
    BEGIN
      UPDATE dbname.tablename
      SET filepathfield = LEFT(filepathfield, LOCATE('\Folder4\',filepathfield,1)+8) || 'ITP' || CHAR(YEAR(current timestamp)*100 + MONTH(current timestamp))
      WHERE idfield = 'idfieldvalue'
    END

The problem with this is that it requires the last folder to be named "Folder4". We would like this to work no matter what the last folder is named.

Is there a way in SQL PL to do the same thing, but to simply apply the change after the last backslash, without having to hard code the last folder name?

Thanks

Answer 1

What about counting "\" - you could simply change your statement to

...LOCATE_IN_STRING(filepathfield,'\',1,5)... ?

This means of cause that the "depth" of your path is as described and the fifth "\" ist the one you are looking for.