I'm trying to retrieve last part of string after underscore.. but every record has different number of underscore and I'm not sure how to write it correctly.

Example:

aaa_bb_cccc_dddd_ee - only ee
aaa_bb_cccc_dddd - only dddd
sss_aas_ww_ww_ww_bb - only bb

As you can see there is different number of underscore and I need only last part after last underscore.

I have been playing with regex and split_part but since I don't now how to point to the last _ then it's not working correctly.

My Idea is to start reading string from the right a then you just pick the first one but couldn't find a way how to do it.

It's probably basic thing but I'm struggling with it so please help.

CodePudding user response：

Use REGEXP_SUBSTR:

SELECT col, REGEXP_SUBSTR(col, '_([^_] )$', 1, 1, 'e', 1) AS col_out
FROM yourTable;