1

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

Leafer
  • 15
  • 5
  • 2
    [ON CONVERSION ERROR](https://stackoverflow.com/questions/4486949/safe-to-number/45886745#45886745) - it will return predefined value instead error – Lukasz Szozda Dec 06 '20 at 16:24
  • I think you’re missing some explanation, if that query returns results then you have successfully returned the two parts of the string as numbers. What query was raising the error? If it was the to_number using just the column name as input then that’s expected to not work - you need to do the splitting first (which you do in the large query). – Andrew Sayer Dec 06 '20 at 17:06
  • Hey Andrew, sorry I messed up there. I just edit my question. But the problem is when I am adding TO_NUMBER fuinction before those splitting formulas (substr.... in X, and before trim... in Y). – Leafer Dec 06 '20 at 17:24
  • 1
    Okay, so you have some data in those strings that doesn’t quite match what Oracle expects a number to look like in a string. The first step would be to identify these rows and then you can make a decision on what needs to be done - do they need removing or do they need some further fixing first? How you identify these will depend on what version of Oracle you’re running, if 12.2+ you can use the `validate conversion` function https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/VALIDATE_CONVERSION.html otherwise you’d need a custom function – Andrew Sayer Dec 06 '20 at 17:29
  • Hey, thanks Andrew. I found that dot was causing the problem, I replaced it by comma and it's working now. – Leafer Dec 06 '20 at 18:14
  • 2
    The *real* question is: Why do you even store the two numbers as strings? Store the two numbers in two separate numeric columns and validity is guaranteed. – Thorsten Kettner Dec 06 '20 at 18:17
  • @Leafer - if "dot" was causing the problem and "comma" works, instead of changing your data, you can specify the decimal delimiter in the calls to TO_NUMBER(). With that said, consider Thorsten K's comment: if at all possible, change the data model, so that it satisfies First Normal Form (by storing only atomic bits of data in each column). –  Dec 07 '20 at 02:27

1 Answers1

1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! I had to replace dot by comma, but it's working also substring function are much more clear than those I used. – Leafer Dec 06 '20 at 18:31
  • @Leafer - actually, no. By some luck, the only issue in your data was "comma" vs "dot" for decimal delimiter. But if it had been different - for example, if you had "numbers" like 3.228E18, or even abc132.2de (obviously not a number at all), Gordon's solution would simply pick the numeric substrings and you would never even know you had bad data in your table. (And, very likely, you will get wrong results and you won't even know it.) –  Dec 07 '20 at 02:30