Get a word after specific word using regexp_substr in sql oracle

You may use

select regexp_substr ('sys: error: This is a message ''123:'' for column EMP_NB', 'column[[:space:]]*([[:alpha:]_]+)', 1, 1, NULL, 1) from dual

Here,

• column - matches column word
• [[:space:]]* - 0 or more whitespace chars
• ([[:alpha:]_]+) - captures into Group 1 any one or more letters or underscores.

The value captured is returned only, since the last group ID argument is set to 1.

Just anchor the pattern to the end of the string:

select regexp_substr('sys: error: This is a message ''123:'' for column EMP_NB', '[[:alpha:]_]+$')from dual

Here is a db<>fiddle.

Your example data has a period at the end. If that is part of the string, then you can use regexp_replace():

select regexp_replace(message, '^.*[^[:alpha:]_]([[:alpha:]_]+)[^[:alpha:]_]*$', '\1')from (select 'sys: error: This is a message ''123:'' for column EMP_NB' as message from dual union all      select 'sys: error: This is a message ''45346:'' for column EM_NM.' as message from dual union all      select 'sys: error: This is a message ''78324f9:'' for column DEPT_NO_VL.' as message from dual     ) x