i have a column href which contiens a liste of links

My ouput would be

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

CodePudding user response：

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#C3.3_.pdf     | 100712107#C3.3  |      
100712107~#C3.2_C00.zip | 100712107~#C3.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 .

CodePudding user response：

You want add a column to the table ?

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