Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    25

    Unanswered: IP Address stored as Binary(4)

    But, I want to display normally in report. This is just something I am not familiar with.

    My IP Address for 144.197.174.0 is stored as 00AEC590. How do I convert that binary(4) back to its original format?

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I'm not connected to DB so I can't test it, but my suggestion is:

    your hex: 00AEC590 represent your IP

    00 = 0
    AE = 174
    C5 = 197
    90 = 144

    so split your string to four char(2) and convert them from HEX to DEC using dump() or similar function.

    substr(IP, 7,2) returns 90
    substr(IP, 5,2) returns C5
    substr(IP, 3,2) returns AE
    substr(IP, 1,2) returns 00

    then put strings together using || and separate them by '.' something like:
    Code:
    select dump(substr(IP, 7, 2)...) || '.' || dump(substr(IP, 5, 2)...) || '.' || dump(substr(IP, 3, 2)...) || '.' || dump(substr(IP, 1, 2)...);
    see manual how to use dump()

  3. #3
    Join Date
    Oct 2005
    Posts
    25
    Yes, I agree with the substr/concatenation solution, however, I want to convert the HEX values to decimal for display...

    ipaddress = 00AEC590

    therefore

    substr(ipaddress,7,2)||'.'||substr(ipaddress,5,2)| |'.'||substr(ipaddress,3,2)||'.'||substr(ipaddress ,1,2)

    will yield something like 90.C5.AE.00. Now I need to get to

    144.197.174.0

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    use dump() for conversion. I'm not familiar with this function and I'm not connected to DB (I can't test it), so you have to do it your self. use manuals, helps, examples, google. I'm sure finaly you'll sort this out.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Code:
    SQL> col ip_address format a15
    SQL> 
    SQL> SELECT TO_NUMBER(SUBSTR(val,7,2),'XX') || '.' ||
      2         TO_NUMBER(SUBSTR(val,5,2),'XX') || '.' ||
      3         TO_NUMBER(SUBSTR(val,3,2),'XX') || '.' ||
      4         TO_NUMBER(SUBSTR(val,1,2),'XX') AS ip_address
      5  FROM   ( SELECT '00AEC590' AS val FROM dual ) b;
    
    IP_ADDRESS
    ---------------
    144.197.174.0
    
    1 row selected.
    Last edited by WilliamR; 11-29-05 at 19:00.

  6. #6
    Join Date
    Oct 2005
    Posts
    25
    Perfect!

    Thanks for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •