Oracle: Store "5-" as a number

Question

SHORT VERSION: What is the best way of casting "5-" as a number for storing into a table as a number?

LONG VERSION: Here is the issue, I have a table I am reading from primary made up of VARCHAR2s. I am grabbing the data from some fields and storing them in another table without modification. In one field I am getting "5-" and trying to store it in a NUMBER field which is throwing an error because it can't cast it as a number.

Can anyone tell me if there is a best practice for dealing data such as this? This seems like it would be a common financial problem. I am sure I could remove '-'s and then if found affix it to the front but I would like input on the best way of dealing with this.

Answer 1

To provide a complete answer, we would need to know the format of values, besides the one '5-' in the varchar2 field. (Please update your question.)

Either of the following will convert a '5-' to the number -5:

to_number('5-', '9S')
to_number('5-', '9MI')

1. If you can have more than one digit, the number of nines must be increased.
2. If you have seperators, such as commas, they must be taken into account.
3. In the case of the '9S' format, a plus sign must be provided for positive numbers. (The number 5 would need to be '5+' because '5' and '5 ' will throw an error.) In the case of the '9MI' format, a space must be provided for positive numbers. ('5 ' is acceptable, but '5' will throw an error).

4. And this would not work if the same column has values in both '-5' and '5-' format. If it is only a few '9999-' numbers that are throwing you, and all the rest convert fine using your current method, I'd test the last character to see if it were negative and if so strip it off, convert the rest, and multiply by -1:

   Table dropped.
   
   
   SQL> create table t (v varchar2(10));
   
   
   Table created.
   
   
   SQL> insert into t values ('-5');
   
   
   1 row created.
   
   
   SQL> insert into t values ('5-');
   
   
   1 row created.
   
   
   SQL> insert into t values ('9999-');
   
   
   1 row created.
   
   
   SQL> insert into t values ('5');
   
   
   1 row created.
   
   
   SQL> select v
     2      , case when substr(v, length(v)) = '-'
     3         then -1 * cast(substr(v, 1, length(v) - 1) as number)
     4         else cast(v as number) end as V_NUM
     5  from t;
   
   
   V               V_NUM
   ---------- ----------
   -5                 -5
   5-                 -5
   9999-           -9999
   5                   5
   
   
   SQL> spool off