CODEWITHSUNDEEP
regexpostgresql
Sameer
Sameer

Reputation: 5188

How to get string by removing middle part in postgres

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)

Upvotes: 0

Views: 313

Answers (2)

COY
COY

Reputation: 704

Single occurrence is easy to handle with regexp_replace like:

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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.

Upvotes: 0

Related Questions