'How to remove everything after first instance of specific delimiter, then before last instance of specific delimiter using Regex? - SQL

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

Input Table:

file_paths
my-file-path/wefw/wefw/wef/wfweof=wefonwe/wfewoi=weoif/
my-file-path/wefw/wef/ef=wefonwe/wfewoi=weoif/
my-file-path/wef/wfe/wefw/wef/ef=wefonwe/wfewoi=weoif/

I want to remove everything after the first = sign, then remove everything before the = sign and after the last / sign.

Output:

file_paths
my-file-path/wefw/wefw/wef/
my-file-path/wefw/wef/
my-file-path/wef/wfe/wefw/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!

regexamazon-redshiftregexp-replace


Solution 1:[1]

You may use:

SELECT REGEXP_REPLACE(file_paths, '[^/=]+=.*', '', 1, 'i')
FROM my_table;

Here is a regex demo showing that the replacement logic is working.

Solution 2:[2]

You may try this regex:

^(.*?\/)(?=[^\/]+=).*$

and replace it by group 1

Demo

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Mustofa Rizwan

Related Questions