Search code examples
regexamazon-redshiftregexp-replace

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!

Solution

  • 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.