CODEWITHSUNDEEP
sqloracle-databasesqldatatypesvarchar2
Leafer
Leafer

Reputation: 15

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

Upvotes: 1

Views: 838

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 1

Related Questions