CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperdata-manipulationdml
MOT
MOT

Reputation: 91

Extract numeric from string into new columns

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

Return table from above query

I would like the table to return:-

Correct table display

Thanks for your help in advance!

Upvotes: 0

Views: 229

Answers (2)

Jonas Metzler
Jonas Metzler

Reputation: 5975

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

Upvotes: 0

Trung Duong
Trung Duong

Reputation: 3475

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

Upvotes: 1

Related Questions