sqloracle-databasesqldatatypesvarchar2

SQL Oracle - problem with converting varchar2 to number


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

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

results from above query


Solution

  • 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