Home » SQL & PL/SQL » SQL & PL/SQL » Number to Bit Function
icon5.gif  Number to Bit Function [message #183429] Thu, 20 July 2006 20:49 Go to next message
pulkit_a
Messages: 50
Registered: December 2005
Member
Hi,

I have to convert Number into Bit format...Cn any one suggest if there is any function in Oracle that i can use for the same.

Regards
Pulkit
Re: Number to Bit Function [message #183430 is a reply to message #183429] Thu, 20 July 2006 21:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Don't know of any function, but this seems to work:
SELECT
     REVERSE (
       MAX (
         REPLACE (
           SYS_CONNECT_BY_PATH (
             SIGN (
               BITAND ( num, POWER ( 2, LEVEL - 1 ) )
             )
           ,'|'
           )
         ,'|', NULL
         ) 
       ) 
     ) AS val
FROM (
    SELECT 38565 AS num
    FROM DUAL 
)
CONNECT BY POWER ( 2, LEVEL - 1 ) <= num


Ross Leishman
Re: Number to Bit Function [message #183437 is a reply to message #183430] Thu, 20 July 2006 22:28 Go to previous messageGo to next message
pulkit_a
Messages: 50
Registered: December 2005
Member
Thanks..A lot... But i am unable to understans its functionality...
Will it be possible for u to explain how this query works in terms of the functions used in it.

Regards
Pulkit
Re: Number to Bit Function [message #183440 is a reply to message #183429] Thu, 20 July 2006 22:48 Go to previous messageGo to next message
pulkit_a
Messages: 50
Registered: December 2005
Member
Can u please tell me why u used

BITAND ( 38564, POWER ( 2, LEVEL - 1 ) )

BITAND ( 38564, POWER ( 2, -1 ) )

BITAND ( 38564, 0.5) )


Regards
Pulkit
Re: Number to Bit Function [message #183488 is a reply to message #183440] Fri, 21 July 2006 02:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I considered it briefly, but it will take about half an hour for me to type in a full explanation - roughly the same time it will take you to work it out by disecting my code, trying examples of subsets of the code.

Hmmm, whose half-hour is most important to me....

Ross Leishman
Re: Number to Bit Function [message #183493 is a reply to message #183488] Fri, 21 July 2006 03:02 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Yeah, Ross's query is good.
And this version of query is Ross's without REVERSE function which is undocumented and so not recommended.

SELECT     
  REPLACE(
    MAX(
      SYS_CONNECT_BY_PATH(
        SIGN (
          BITAND (deci, POWER(2, (CEIL(LOG(2, deci)) - LEVEL)))
        )
      , ','
      )
    )
  , ','
  ) bit
FROM (SELECT 38565 deci
        FROM DUAL)
CONNECT BY POWER (2, LEVEL - 1) <= deci

[Updated on: Mon, 17 March 2008 02:29] by Moderator

Report message to a moderator

Re: Number to Bit Function [message #302000 is a reply to message #183429] Fri, 22 February 2008 11:41 Go to previous messageGo to next message
Frank_Zhou
Messages: 5
Registered: February 2008
Location: Braintree , MA
Junior Member
zozogirl's query is a nice try of the problem, but the SQL didn't handle some of the special cases.
For example when the input is 2, 4 ....etc


SQL> SELECT
  2    REPLACE(
  3      MAX(
  4        SYS_CONNECT_BY_PATH(
  5          SIGN (
  6            BITAND (deci, POWER(2, (CEIL(LOG(2, deci)) - LEVEL)))
  7          )
  8        , ','
  9        )
 10      )
 11    , ','
 12    ) bit
 13  FROM (SELECT 2 deci
 14          FROM DUAL)
 15  CONNECT BY POWER (2, LEVEL - 1) <= deci;

BIT                                                                             
--------------------------------------------------------------------------------
00                                                                              

SQL> SELECT
  2    REPLACE(
  3      MAX(
  4        SYS_CONNECT_BY_PATH(
  5          SIGN (
  6            BITAND (deci, POWER(2, (CEIL(LOG(2, deci)) - LEVEL)))
  7          )
  8        , ','
  9        )
 10      )
 11    , ','
 12    ) bit
 13  FROM (SELECT 4 deci
 14          FROM DUAL)
 15  CONNECT BY POWER (2, LEVEL - 1) <= deci
 16  ;

BIT                                                                             
--------------------------------------------------------------------------------
000                                                                             

Here is an alternative sql solution without using the undocumented "reverse" function.


SQL> SELECT utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(to_char(
  2         MAX (REPLACE(SYS_CONNECT_BY_PATH(SIGN(BITAND(num, POWER(2,LEVEL - 1)))
  3                        ,','),',')) )))) AS val
  4  FROM (SELECT 2 AS num FROM DUAL )
  5  CONNECT BY POWER ( 2, LEVEL - 1 ) <= num;

VAL                                                                             
--------------------------------------------------------------------------------
10                                                                              

SQL> SELECT utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(to_char(
  2         MAX (REPLACE(SYS_CONNECT_BY_PATH(SIGN(BITAND(num, POWER(2,LEVEL - 1)))
  3                        ,','),',')) )))) AS val
  4  FROM (SELECT 4 AS num FROM DUAL )
  5  CONNECT BY POWER ( 2, LEVEL - 1 ) <= num;

VAL                                                                             
--------------------------------------------------------------------------------
100

[Updated on: Fri, 22 February 2008 12:02] by Moderator

Report message to a moderator

Re: Number to Bit Function [message #302005 is a reply to message #302000] Fri, 22 February 2008 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/96535/297439/102589/#msg_297439

Regards
Michel
Re: Number to Bit Function [message #306787 is a reply to message #183429] Sun, 16 March 2008 17:42 Go to previous messageGo to next message
sperry
Messages: 1
Registered: March 2008
Junior Member
An alternative...



select
    NVL
      (
        LTRIM
          (
            REPLACE
              (
                SYS_CONNECT_BY_PATH
                  (
                    DECODE
                      (
                        SUBSTR (HEX, LEVEL, 1),
                        '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'
                      ),
                    '|'
                  ),
               '|',
               null
             ),
           '0'
          ),
        '0'
     ) as BINARY
from
    -- // inline view (convert to hex first)
      (
        select
            TO_CHAR (38565, 'XXXXXXXX') as HEX -- // 32 bits
        from
            DUAL
      )
where
    LEVEL = 9 -- // i.e. 32 / 4 + 1
connect by
    LENGTH (SUBSTR (HEX, LEVEL, 1)) > 0
;

Re: Number to Bit Function [message #306857 is a reply to message #183429] Mon, 17 March 2008 02:14 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Heh, just found this.

http://www.orafaq.com/wiki/Binary
Previous Topic: SEND Query Output By email
Next Topic: count in dynamic SQL (merged 2)
Goto Forum:
  


Current Time: Thu Dec 08 04:32:38 CST 2016

Total time taken to generate the page: 0.05605 seconds