How to select the part of a column between _ and a

Question

i have a column href which contiens a liste of links

href
100712107%23C3.3_.pdf
100712107~%23C3.2_C00.zip
100740104_C2.3.xls
testme_aze_--b00.xls

My ouput would be

href	name	rev
100712107%23C3.3_.pdf	100712107%23C3.3
100712107~%23C3.2_C00.zip	100712107~%23C3.2	C00
100740104_C2.3.xls	100740104	C2.3
testme_aze_--b00.xls	testme_aze	--b00

I have tried to use ** reverse(split_part(reverse(href),'.',1))**

Does this answer your question? [How to extract a substring pattern in Postgresql](https://stackoverflow.com/questions/31381174/how-to-extract-a-substring-pattern-in-postgresql) — PM 77-1, Nov 22 '21 at 17:58

score 1 · Accepted Answer · answered Nov 22 '21 at 18:12

You can use a regex together with substring() to extract the parts you want:

select href, 
       substring(href from '(.*)_') as name,
       substring(href from '.*_(.*)\.') as rev
from the_table

returns:

href                      | name              | rev  
--------------------------+-------------------+------
100712107%23C3.3_.pdf     | 100712107%23C3.3  |      
100712107~%23C3.2_C00.zip | 100712107~%23C3.2 | C00  
100740104_C2.3.xls        | 100740104         | C2.3 
testme_aze_--b00.xls      | testme_aze        | --b00

substring() will return the first group from the regex. The firs regex matches all characters until the last _.

The second expression matches everything after the last _ up until the last .

score -1 · Answer 2 · answered Nov 22 '21 at 17:56

-1

You want add a column to the table ?

ALTER TABLE TABLE_NAME ADD COLUMN HREF VARCHAR(255) NOT NULL;

answered Nov 22 '21 at 17:56

Code Awesome

181
1
5

How to select the part of a column between _ and a

2 Answers2

You want add a column to the table ?