I have problem with converting varchar2 to number,
I have a column with such a data, which is a varchar2 (below, few records from this column)
POINT(-122.387539744377 37.7604575554348)
POINT(-122.400868982077 37.7985721084626)
POINT(-122.3904285 37.7896767)
POINT(-122.397404909134 37.7875217804563)
POINT(-122.421567589045 37.7941604417493)
I need to calculate something, but I need split those points into X and Y number, I managed to substring those points into 2 columns, but when I am trying to covnert it to number to_number(column_name) I have an error
- 00000 - "invalid number" *Cause: The specified number was invalid.
That's my query, I wanted to add to_number function before X, and Y columns (before substr and trim functions), but it's resulting with a error as above)
select substr(COLUMN_NAME 7, instr(COLUMN_NAME, ' ')-7)) as X,
trim(trailing ')' from substr(COLUMN_NAME, length(substr(COLUMN_NAME, 0, instr(COLUMN_NAME, ' '))), 50)) as Y
from TABLE_NAME;
You can start by only extracting numbers:
select regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 1),
regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 2)
from (select 'POINT(-122.387539744377 37.7604575554348)' as str from dual) x
The results should work with to_number()
:
select to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 1)),
to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 2))
from (select 'POINT(-122.387539744377 37.7604575554348)' as str from dual) x;
Oracle now supports error handling, so you can include that as well:
select to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 1) default null on conversion error),
to_number(regexp_substr(str, '-?[0-9]+[.][0-9]+', 1, 2) default null on conversion error)
from (select 'POINT(-122.387539744377 37.7604575554348)' as str from dual) x