OK, you can use regexp_replace
to remove the file
and only reserve the dir path, as we know the file name does not contain the character '/' and is always located at the end of the dir path, so the regexp can be written as '[^/]+$'
, the examples as below, it means that replace the substring with regexp '[^/]+$'
to an empty ''
.
select regexp_replace('/this/is/my/dir/file','[^/]+$','') as dir;
+-------------------+
| dir |
+-------------------+
| /this/is/my/dir/ |
+-------------------+
select regexp_replace('this/is/my/another/dir/file','[^/]+$','') as dir;
+--------------------------+
| dir |
+--------------------------+
| this/is/my/another/dir/ |
+--------------------------+