Convert Binary's 2's compliment to decimal in oracle SQL-CodePudding

I have a sample number in a column of oracle table which is binary's 2's complimanet - e.g 0110001000110111

I want to convert this to normal decimal number in 2's compliment.

Expected output-

Reference link - https://www.rapidtables.com/convert/number/decimal-to-binary.html

CodePudding user response：

You can loop as applying powers of 2 while multiplying by each bit(0 or 1) starting from the right end of your presented value such as

SET SERVEROUTPUT ON

DECLARE
  bin_nr VARCHAR2(100) := '0110001000110';
  dec_nr NUMBER;
BEGIN
 FOR i IN 1..LENGTH(bin_nr)
  LOOP 
   dec_nr := NVL(dec_nr,0)   SUBSTR(bin_nr,-i,1)*(2**(i-1));   
  END LOOP;
   DBMS_OUTPUT.PUT_LINE(dec_nr);
END; 
/

which results 3142 as the decimal value.

Demo

CodePudding user response：

For 2s compliment, the most-significant bit represents the sign bit and if that is 1 then you have a negative number:

DECLARE
  bin_nr  VARCHAR2(100) := '1111001110111010';
  sign    PLS_INTEGER;
  dec_nr  PLS_INTEGER;
BEGIN
  IF SUBSTR(bin_nr, 1, 1) = '1' THEN
    bin_nr := TRANSLATE(bin_nr, '01', '10');
    sign   := -1;
    dec_nr := 1;
  ELSE
    sign   := 1;
    dec_nr := 0;
  END IF;
  FOR i IN 1 .. LENGTH(bin_nr) LOOP
    IF SUBSTR(bin_nr, -i, 1) = '1' THEN
      dec_nr := dec_nr   POWER(2, i-1);
    END IF;
  END LOOP;
  dec_nr := dec_nr * sign;
  DBMS_OUTPUT.PUT_LINE(dec_nr);
END;   
/

Outputs -3142

If you are expecting an N-bit binary number as the input (for example, the link in the question expects a 16-bit binary number as an input for 2s compliment) then you should LPAD with zeroes if you have fewer than that many bits.

db<>fiddle here