title: Change folder path in field with SQL-PL
body: <p>I've got a field in a DB2 table which holds a file path. The value will look something like this:</p>
  
  <p>N:\Folder1\Folder2\Folder3\Folder4\ITP201602</p>
  
  <p>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.</p>
  
  <p>Here is my current query, which works:</p>
  
  <pre><code>   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
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Thanks</p>