find last position of char in string in query

user_17614576 0
Viewed 39

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?

postgresql sql
2 Answers

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

user_58413060

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}.')
user_176145760
Related