CODEWITHSUNDEEP
sqloracle-database
Javi Torre
Javi Torre

Reputation: 824

Oracle SQL Convert to negative number

Is there any way to convert the following value 72.86- into the number -72.86?

Upvotes: 0

Views: 3167

Answers (2)

user5683823
user5683823

Reputation:

I know you already selected a "correct answer" - which also already has two upvotes - but that is a very poor, amateur level solution.

The proper way to handle your input is to simply use the correct format model with the to_number function. For example:

select to_number('72.86-', '999.99mi') as my_number
from   dual;

 MY_NUMBER
----------
    -72.86

Notice the mi format model element - it means "minus sign, if present, is trailing". See https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570

EDIT: Here is how the same can be used on a column with mixed format for positive numbers (notice the fm format modifier):

select str, to_number(str, 'fm999.99mi') as my_number
from
       (
         select '92.93-' as str from dual union all
         select null            from dual union all
         select '33.9+'         from dual union all
         select '6'             from dual union all
         select '0.00'          from dual
       )
;

STR     MY_NUMBER
------ ----------
92.93-     -92.93
                 
33.9+        33.9
6               6
0.00            0

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Presumably, the value is a string. You can use:

(case when col like '%-'
      then - to_number(replace(col, '-', ''))
      else to_number(col)
 end)

Note that this will work for any character that might appear in the last position, including em-dashes, en-dashes, and characters from other character sets.

Upvotes: 1

Related Questions