find last position of char in string in query-CodePudding

I need to copy rows, but I also need, to change path field:

old path value = '<src_dir>'/workspace_id/project_id/file_id.file_format

new path value = '<src_dir>'/workspace_id/new_project_id/new_file_id.file_format

I tried to find the dot position and count from it two uuid lengths slash and put there new slash-separated project and file IDs

overlay(path, placing '{<new_project_id>}/{<new_file_id>}' from (position('.' in path)-(36 * 2   1) for (36 * 2   1)))

But if a src_dir contains a dot in its name, the position of that dot will be taken. Is there any way to take the position of the last dot?

CodePudding user response：

You can use REVERSE() function along with LENGTH() such as

SELECT LENGTH(path) - POSITION( '.' IN REVERSE(path))   1
  FROM t

Demo

this case the last dot would be positioned as the first

CodePudding user response：

Okay, I am not pro in regexp, it can it could be much prettier, but it works:

regexp_replace(path, '([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})\/(([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})\.)', '{new_project_id}/{new_file_id}.')