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!
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