How to get string by removing middle part in postgres

Question

I want to remove middle string which is date and get the value in postgres

Example
From this

ABC_XYZ_20200129041836.csv or 2ABC_XYZ_20200129041836.txt

to this

ABC_XYZ.csv or 2ABC_XYZ.txt

I tried this regex [^_]+$. which selects all strings after last occurence of _ including after . (e.g., _20200129041836.csv)

COY · Accepted Answer · 2020-03-07 06:10:42Z

1

Single occurrence is easy to handle with regexp_replace like:

select regexp_replace('ABC_XYZ_20200129041836.csv', '(.*)_[0-9]{14}([_.].*)', '\1\2');

answered Mar 7, 2020 at 6:10

COY

7043 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sameer Over a year ago

Your answer works for me but what if I have ABC_XYZ_ABC_XYZ_20200129041836123.csv - any number not limited to 14

COY Over a year ago

@SameerKhan You can simply change the {14} part to a range. Maybe {14, 17} if it could have milliseconds. This answer in fact aims to help when you have filenames like 'ABC_XYZ_20200129041836_v2.csv', from the question subject, I didn't see an assumption that the time part is always right before the file extension.

COY Over a year ago

In case you need more precise detection of dates, this article may help: regular-expressions.info/dates.html

Tim Biegeleisen · Accepted Answer · 2020-03-07 06:04:02Z

0

I would use REGEXP_REPLACE here:

SELECT
    filename,
    REGEXP_REPLACE(filename, '^([^_]+_[^_]+)_.*(\..*)$', '\1\2') AS filenameshort
FROM yourTable;

The strategy here is to match and capture the first two portions of the filename occurring in between the _ separators in one capture group, along with the extension in a second capture group. Then, we replace with just that first and second capture group.

answered Mar 7, 2020 at 6:04

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

8 Comments

Sameer Over a year ago

Thanks! This is what I want. BTW what is \1\2, I think concat matching group right ?

Tim Biegeleisen Over a year ago

Yes it is concatenating the two capture groups. Note that typically to concatenate two strings || is required though.

Sameer Over a year ago

Hey! what if I have this string ABC_XYZ_ABC_XYZ_20200129041836123.csv.

Tim Biegeleisen Over a year ago

...and, what output would you expect in that case?

Sameer Over a year ago

Output should be ABC_XYZ_ABC_XYZ.csv

|

Collectives™ on Stack Overflow

How to get string by removing middle part in postgres

2 Answers 2

3 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related