Extract numeric from string into new columns-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

If you want to keep your SUBSTRING idea, you can use VALIDATE_CONVERSION to check whether your substring is numeric or not.

Then a CASE will select this substring or NULL if not numeric:

SELECT x, 
CASE WHEN VALIDATE_CONVERSION(SUBSTR(x, INSTR(x,' ', -1)   1) AS NUMBER) = 1
  THEN SUBSTR(x, INSTR(x,' ', -1)   1)
  ELSE NULL END AS y
FROM yourtable;

Try out db<>fiddle

Here the documentation of VALIDATE_CONVERSION