0

I want to get decimal value of the given ipaddress in SQL example 192.168.0.0 its decimal representation would be 3232235520. IP - DECIMAL

select 
       TO_NUMBER(LISTAGG(binary1, '') WITHIN GROUP ( ORDER BY cnt), 'XXXXXXXX') as Numeric1
from (
     select 1 as grp, rownum as cnt
            , NVL(TRIM(LEADING '0' FROM utl_raw.cast_from_binary_integer(regexp_substr('192.168.255.255','\d+', 1, rownum))), '00') as binary1
          from dual connect by rownum <= 4
     ) temp
group by grp

I am using oracle 11g and I have come up with the above approach.

  1. convert the octet into binary representation using ult_raw.cast_from_binary_integer but internally binary gets converted to hex representation. Therefore the output of the inner query is basically hex representation of the octet.
  2. Hex representation is obtained by clubbing 4 binarys example (1000)2 = (8)16 therefore getting decimal value of (10001000)2 is equal to getting the value for (88)16
  3. I have appended the hex representation preserving the order of octet with cnt column and clubbing the result with LISTAGG

UPDATE

I am using LISTAGG and connect by clauses does this have any performance issues when I try to convert 1000s of records. this method as suggested is simple and readable but there is no mathematical explanation. second method this uses PL/SQL functions, i will look at this as my last resort.

Community
  • 1
  • 1
saikumarm
  • 1,565
  • 1
  • 15
  • 30
  • 1
    How are you defining 'better'? [This method](http://stackoverflow.com/a/14994215/266304) gets the same decimal value from your sample address. Or [this answer](http://stackoverflow.com/a/1085622/266304) has functions to convert both ways, which might be useful at some point. – Alex Poole Jan 06 '16 at 17:46

1 Answers1

0

Mathematical Explanation for this method is as follows

SELECT
TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,1))*POWER(2,24)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,2))*POWER(2,16)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,3))*POWER(2,8)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,4))*POWER(2,0) IP
FROM
DUAL;

suppose I have IPADDRESS = 255.127.72.1

binary representation would be 11111111.01111111.01001000.00000001

converting to decimal 

first octet
1.231 + 1.230 + 1.229 + 1.228
+ 1.227 + 1.226 + 1.225 + 1.224

= (1.27 + 1.26 + 1.25 + 1.24 
   + 1.23 + 1.22 + 1.21 + 1.1).224
= (28 - 1).224
= (255).224

second octet
0.223 + 1.222 + 1.221 + 1.220
+ 1.219 + 1.218 + 1.217 + 1.216

= (0.27 + 1.26 + 1.25 + 1.24 
   + 1.23 + 1.22 + 1.21 + 1.1).216
= (27 - 1).216
= (127).216

third octet
0.215 + 1.214 + 0.213 + 0.212
+ 1.211 + 0.210 + 0.29 + 0.28

= (0.27 + 1.26 + 0.25 + 0.24 
   + 1.23 + 0.22 + 0.21 + 0.1).216
= (26 + 1.23).216
= (72).216

similarly the last octet

I was able to eliminate sub query with the below approach

select sum(regexp_substr('255.127.72.1', '\d+', 1, rownum) * POWER(2, 32 - 8*rownum))  as binary1
          from dual connect by rownum <= 4
Community
  • 1
  • 1
saikumarm
  • 1,565
  • 1
  • 15
  • 30