How to strip ending date from string using Regex?

I want to format the strings in a table column, in a specific format.

Input Table:

file_paths
my-file-path/wefw/wefw/2022-03-20
my-file-path/wefw/2022-01-02
my-file-path/wef/wfe/wefw/wef/2021-02-03
my-file-path/wef/wfe/wef/

I want to remove everything after the last / sign, if the only thing after it resembles a date (i.e. YYYY-MM-dd or ####-##-##).

Output:

file_paths
my-file-path/wefw/wefw/
my-file-path/wefw/
my-file-path/wef/wfe/wefw/wef/
my-file-path/wef/wfe/wef/

I'm thinking of doing something like:

SELECT regexp_replace(file_paths, 'regex_here', '', 1, 'i')
FROM my_table

I'm unsure of how to write the RegEx for this though. I'm also open to easier methods of string manipulation, if there are any. Thanks in advance!

CodePudding user response：

Here is a solution using regexp_replace.

select regexp_replace(val, '^(.*?\/)\d{4}-\d{2}-\d{2}$', '\1')
FROM file_paths;

| regexp_replace                 |
| :----------------------------- |
| my-file-path/wefw/wefw/        |
| my-file-path/wefw/             |
| my-file-path/wef/wfe/wefw/wef/ |
| my-file-path/wef/wfe/wef/      |
| numbers123/wef/wfe/wef/        |
| numbers123/wef/wfe/wef/        |

db<>fiddle here

CodePudding user response：

You may use this regex:

^(.*?\/)\d{4}-\d{2}-\d{2}$

You may try this query:

select regexp_replace(file_paths, '^(.*?\/)\\d{4}-\\d{2}-\\d{2}$','$1')

Demo