Home » SQL & PL/SQL » SQL & PL/SQL » Number to Bit Function
 Number to Bit Function [message #183429] |
Thu, 20 July 2006 20:49  |
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  |
rleishman
Messages: 3728
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 |
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 #183488 is a reply to message #183440] |
Fri, 21 July 2006 02:47 |
rleishman
Messages: 3728
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 |
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 |
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 #306787 is a reply to message #183429] |
Sun, 16 March 2008 17:42 |
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
;
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Oct 06 16:35:53 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
