CODEWITHSUNDEEP
oracle-databaseplsqlsigned
Marianne
Marianne

Reputation: 43

How to convert signed numbers in Oracle SQL

I have a table that contains signed numbers ('123C' = 1233). Is there a way to convert these numbers in a select statement in PL/SQL (ie. +1233) so I can load them into another table? I am not really even sure how to google that question.

Thanks!

Upvotes: 1

Views: 1968

Answers (2)

Alex Poole
Alex Poole

Reputation: 191520

Based on the rule that the last character represents +1 as A, +2 as B, +3 as C, etc.; and -1 as J, -2 as K, -3 as L etc., you can do a two-stage conversion. First replace the last character with the corresponding digit:

translate(value, 'ABCDEFGHIJKLMNOPQR', '123456789123456789')

You could do something with ASCII comparison but a simple list is probably clearer here. And then convert that to a number:

to_number(translate(value, 'ABCDEFGHIJKLMNOPQR', '123456789123456789'))

And then multiply by -1 if the last character was in the negative range:

case when substr(value, -1) >= 'J' then -1 else 1 end

Demo with your sample values:

with t (value) as (
  select '123C' from dual
  union all select '8268D' from dual
  union all select '680G' from dual
  union all select '269M' from dual
  union all select '535R' from dual
)
select value,
  to_number(translate(value, 'ABCDEFGHIJKLMNOPQR', '123456789123456789'))
      * case when substr(value, -1) >= 'J' then -1 else 1 end as converted
from t;

VALUE  CONVERTED
----- ----------
123C        1233
8268D      82684
680G        6807
269M       -2694
525R       -5259

You could put all of that in a function if you'll use it a lot but it's fairly simple to repeat as needed.

But you haven't specified how you represent the last digit being a zero. If you're using 'zoned decimal' then you can include those in the translate() call:

translate(value, '{ABCDEFGHI}JKLMNOPQR', '01234567890123456789')

But the sign calculation is a little more awkward as you can't just use character set order; again brute-force but clear, you can just compare the last character to decide if it's positive or negative:

    case when substr(value, -1) in ('J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', '}')
      then -1 else 1 end

As a function just for fun:

create function decode_zoned_decimal(p_value in varchar2) return number is
begin
  return to_number(translate(p_value, '{ABCDEFGHI}JKLMNOPQR', '01234567890123456789'))
    * case when substr(p_value, -1) in ('J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', '}')
        then -1 else 1 end;
end;
/

And testing with some additional values:

with t (value) as (
  select '123C' from dual
  union all select '8268D' from dual
  union all select '680G' from dual
  union all select '269M' from dual
  union all select '525R' from dual
  union all select '123{' from dual
  union all select '123}' from dual
)
select value, decode_zoned_decimal(value) as converted
from t;

VALUE  CONVERTED
----- ----------
123C        1233
8268D      82684
680G        6807
269M       -2694
525R       -5259
123{        1230
123}       -1230

Upvotes: 1

are
are

Reputation: 2615

123C sounds as COMP-3 number style

maybe the link will be useful: AskTom, Comp-3 converting

Upvotes: 0

Related Questions