SnowflakeSQL get me last text after specific char

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.

Answer

Use REGEXP_SUBSTR:

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