How to remove a specific part of a string in Postgres SQL?

Question

Say I have a column in postgres database called pid that looks like this:

set1/2019-10-17/ASR/20190416832-ASR.pdf
set1/2019-03-15/DEED/20190087121-DEED.pdf
set1/2021-06-22/DT/20210376486-DT.pdf

I want to remove everything after the last dash "-" including the dash itself. So expected results:

set1/2019-10-17/ASR/20190416832.pdf
set1/2019-03-15/DEED/20190087121.pdf
set1/2021-06-22/DT/20210376486.pdf

I've looked into replace() and split_part() functions but still can't figure out how to do this. Please advise.

Show us your attempt so we can see where we could assist.

Natrium
– Natrium

2023-01-04 09:34:03 +00:00
Commented Jan 4, 2023 at 9:34 — Natrium
– Natrium, Commented Jan 4, 2023 at 9:34

Tim Biegeleisen · Accepted Answer · 2023-01-04 09:39:33Z

3

We can use a regex replacement here:

SELECT col, REGEXP_REPLACE(col, '^(.*)-[^-]+(\.\w+)$', '\1\2') AS col_out
FROM yourTable;

The regex used above captures the column value before the last dash in \1, and the extension in \2. It then builds the output using \1\2 as the replacement.

Here is a working regex demo.

edited Jan 4, 2023 at 9:39

answered Jan 4, 2023 at 9:34

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

amnesic Over a year ago

What is the '\1\2' part after the comma in REGEXP_REPLACE()?

Tim Biegeleisen Over a year ago

@amnesic \1 refers to the first capture group, which is (.*). The \2 is the second capture group and is (\.\w+).

Collectives™ on Stack Overflow

How to remove a specific part of a string in Postgres SQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related