ITPub博客

首页 > 数据库 > Oracle > 用SQL, 十进制转二进制的方法

用SQL, 十进制转二进制的方法

原创 Oracle 作者:peter1166 时间:2016-02-24 11:30:14 0 删除 编辑

 问:1234 转二进制,一个SQL如何写?

---------------------------------------------------------------


方法1: 用递归, 除二取余法

WITH T (A,B) AS (
SELECT FLOOR(1234/2), MOD(1234,2)  FROM DUAL
UNION ALL
SELECT FLOOR(T.A/2) , MOD(T.A,2)
FROM  T
WHERE T.A > 0
)
SELECT LISTAGG(B,'')WITHIN GROUP(ORDER BY A ) BIN FROM T;

-----------------------------------------------------------------------
方法2: 用 MODEL 子句的递归, 除二取余法。 1234 的二进制长度不会大于 CEIL(LOG(2,1234))

SELECT LTRIM(BIN,'0') BIN FROM (SELECT 1234 N FROM DUAL)
MODEL
DIMENSION BY (0 A)
MEASURES(N,CAST('' AS VARCHAR2(2000)) BIN)
RULES ITERATE(1000) UNTIL(ITERATION_NUMBER = CEIL(LOG(2,1234)) )(
  BIN[0] = TO_CHAR(MOD(N[0],2))||BIN[0]
  ,N[0] = FLOOR(N[0]/2)
);


-----------------------------------------------------------------------
方法3: 用 CONNECT BY , 利用位移AND运算,使用2^n 对二进制每位进行是否1的验证的方法。


SELECT LTRIM(REVERSE(REPLACE(SYS_CONNECT_BY_PATH(BITAND(1234,POWER(2,LEVEL-1))/POWER(2,LEVEL-1),','),',')),'0')
FROM DUAL
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY LEVEL <= CEIL(LOG(2,1234))+1;


上面的写法,参考了网友的如下写法。但下面的写法有问题。对 4,8,16 等十进制数据转换长度判断错误,稍微改下就好了。

select replace(sys_connect_by_path(decode(bitand(8, power(2, ceil(log(2, 8)) - level)),
                               0,
                               '0',
                               '1'),
                        '.'),'.',null)
from dual
where level = ceil(log(2, 1234))
connect by level <= ceil(log(2, 8));

-----------------------------------------------------------------------
方法4:十进制先换成十六进制,十六进制再换成二进制, 用了16个 REPLACE , nyfor 写的

SELECT LTRIM(
       REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(TO_CHAR(1234,'FMXXXXXX')
       ,'0','0000')
       ,'1','0001')
       ,'2','0010')
       ,'3','0011')
       ,'4','0100')
       ,'5','0101')
       ,'6','0110')
       ,'7','0111')
       ,'8','1000')
       ,'9','1001')
       ,'A','1010')
       ,'B','1011')
       ,'C','1100')
       ,'D','1101')
       ,'E','1110')
       ,'F','1111'),'0')
FROM DUAL ;

--------------------------------------------------------------------------------
方法5:也是 nyfor 写的

select ltrim(translate(ltrim(dump(convert(translate(to_char(1234, 'FMXXXXXXXX'),
                                                     '0123456789ABCDEF',
                                                     '热壬纫扔扇缮梢捎胰疑乙矣尤由右佑'),
                                           'zhs16gbk')),
                              'TypLen =0123456789'),
                        '123456789, :',
                        '1'),
              '0')
from dual;

--------------------------------------------------------------------------------
方法6:也是 nyfor 写的,最简单的写法了


select ltrim(translate(asciistr(translate(to_char(1234, 'FMXXXXXX'),
                                           '0123456789ABCDEFG',
                                           '业丛个丫会伛伪伫帚帛帪師弚弛弪弫')),
                        '4E1A5F2B\',
                        '00001111'),
              '0')
from dual;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13696961/viewspace-1993567/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-06-26

  • 博文量
    10
  • 访问量
    9686