sql - 按给定序列将二进制转换为十进制

Question

我需要将二进制数转换为十进制数。转换后的十进制范围不大于 256。(一亿二进制)

它涉及一个iOS应用程序，在那里阅读问题调查报告，动态选择四个二进制整数并基于tht。 (一些 UI 里面有 0 和 1)。

然后，我们将它们与 '.' 连接起来。作为分隔符。(类似于IP地址)
根据最终形成的序列，我们将它们转换为小数后获取报告 id。

说，10.100.1.11 (输入)可能会变成 2.4.1.3 (输出)

已经经历了无数的报告和Objective-C。从字面上看，我失去了理智。在 iOS 开发中，我无法编译 PL/SQL。所以，我必须用 SQL 来完成。

编辑:

幸运的是，我能够写出这样的东西。 (但一次只有一个数字:()

SQL> var NUM number;
SQL> exec :NUM := 100000000;

PL/SQL procedure successfully completed.

SQL> SELECT SUM(value)
FROM
  (SELECT POWER(2,LENGTH(TO_CHAR(:NUM))-level)*to_number(SUBSTR(TO_CHAR(:NUM),level,1),'FM9') AS value
  FROM DUAL
    CONNECT BY level <= LENGTH(TO_CHAR(:NUM))
  );  2    3    4    5    6

SUM(VALUE)
----------
       256

Answer 1

有多种方法可以解决这个问题。所以，我选择了一种看起来相当神秘的方式。这样做的动机是 to_number()接受十六进制格式，但不接受二进制格式。支持二进制和八进制以及十六进制有什么困难？嗯，这不是我要问的问题。甲骨文没有。

但是，我们可以轻松地从二进制转换为十六进制。您只处理 8 个二进制数字，因此只有两个十六进制数字。这是代码:

with bin2hex as (
      select '0000' as bin, '0' as hex from dual union all
      select '0001' as bin, '1' as hex from dual union all
      select '0010' as bin, '2' as hex from dual union all
      select '0011' as bin, '3' as hex from dual union all
      select '0100' as bin, '4' as hex from dual union all
      select '0101' as bin, '5' as hex from dual union all
      select '0110' as bin, '6' as hex from dual union all
      select '0111' as bin, '7' as hex from dual union all
      select '1000' as bin, '8' as hex from dual union all
      select '1001' as bin, '9' as hex from dual union all
      select '1010' as bin, 'A' as hex from dual union all
      select '1011' as bin, 'B' as hex from dual union all
      select '1100' as bin, 'C' as hex from dual union all
      select '1101' as bin, 'D' as hex from dual union all
      select '1110' as bin, 'E' as hex from dual union all
      select '1111' as bin, 'F' as hex from dual
     )
select t.*, c1.bin as bin1, c2.bin as bin2, c1.hex as hex1, c2.hex as hex2,
       to_number(c2.hex||c1.hex, 'xx')
from (select '10010010' as num from dual union all
      select '10010' from dual
     ) t left outer join
     bin2hex c1
     on substr('00000000'||t.num, -4) = c1.bin left outer join
     bin2hex c2
     on substr('00000000'||t.num, -8, 4) = c2.bin;