Convert a number to Binary's 2's compliment-

I have a sample number in a column of oracle table - 1647795600

I want to convert this to binary 2's compliment.

Expected output-01100010001101110101110110010000

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

CodePudding user response：

I found a statement which worked fine for sample number tested- select reverse(max(replace(sys_connect_by_path(mod(trunc(&N/power(2,level-1)),2),' '),' ',''))) bin from dual connect by level <= 32 ;

But might not completely useful, need to modify to read from table.

CodePudding user response：

You can create the function:

CREATE FUNCTION dec_to_2c_bin(
  value IN PLS_INTEGER,
  width IN PLS_INTEGER := 32
) RETURN VARCHAR2 DETERMINISTIC
IS
  v_unsigned PLS_INTEGER;
  v_binary   VARCHAR2(201);
BEGIN
  IF value < 0 THEN
    v_unsigned := -1 - value;
  ELSE
    v_unsigned := value;
  END IF;
  WHILE ( v_unsigned > 0 ) LOOP
     v_binary := MOD(v_unsigned, 2) || v_binary;
     v_unsigned := TRUNC( v_unsigned / 2 );
  END LOOP;
  IF LENGTH(v_binary) > width - 1 THEN
    RAISE_APPLICATION_ERROR(-20000, 'The value is too large.');
  END IF;
  v_binary := LPAD(v_binary, width, '0');
  IF value < 0 THEN
    RETURN TRANSLATE(v_binary, '01', '10');
  ELSE
    RETURN v_binary;
  END IF;
END;
/

Then for the sample data:

CREATE TABLE table_name (value) AS
SELECT  1647795600 FROM DUAL UNION ALL
SELECT -1647795600 FROM DUAL UNION ALL
SELECT       25143 FROM DUAL UNION ALL
SELECT        3142 FROM DUAL UNION ALL
SELECT       -3142 FROM DUAL;

The query:

SELECT value, dec_to_2c_bin(value, 64) AS binary2c
FROM   table_name;

Outputs:

db<>fiddle here