Extract numeric from string into new columns

Question

I'm trying to create a new column (y) from another column (x) - my aim is to extract the numeric value after the 1st space of the string from the right-hand side, and if there is no numeric value after the space, then NULL. I used the following SQL query below; however, the query extracted both numeric and non-numeric after the space into the new column (y) - please see the first image below. I have also attempted to use case statement but have yet to achieve the required output.

SELECT x, SUBSTR(x, INSTR(x,' ', -1) + 1) AS y
    FROM  <table_name>;

I would like the table to return:-

Thanks for your help in advance!

Answer 1

You could try regular expression function REGEXP_SUBSTR

SELECT x, REGEXP_SUBSTR (x, '(\s)(\d+)$') AS y
FROM <table_name>

Please check demo here: http://sqlfiddle.com/#!4/7bc0ee/4866