Get text after specific character

Question

I have a field in a Teradata database and I want to extract the text after a specific value. I can't use substring as there is sometimes more than 1 space before the text.

Example 1

Amount changed to Ù95

Example 2

Amount changed to   Ù150

I only want to select the number after Ù. So 95 in example 1 and 150 in example 2. I think I need to use REGEXP_SUBSTR but can't work it out.

Answer 1

You can also do this

select substring('Amount changed to Ù95',INSTR('Amount changed to Ù95','Ù')+1);

Answer 2

You can use the regexp_substr function:

SELECT REGEXP_SUBSTR(your_column_name, 'Amount changed to\s+(\S+)')
FROM your_table;