Convert the IP address and mask in Oracle to the CIDR format

Source: Internet
Author: User


In Oracle, IP addresses and masks are converted to the CIDR format due to the following problems: IP addresses and IP address masks are stored in the database and must be converted to the CIDR format, in addition, it is not only about converting the mask into a number corresponding to the CIDR. You need to convert the original IP address to the corresponding network address. For example, the IP address is 58.247.221.238 and the mask is 255.255.255.252, convert it to 58.247.221.236/30. Www.2cto.com solution: We know that you can get the corresponding network address by using the IP address and mask through the bitwise and function. Google, find the function that converts the IPv4 address to a number and converts it back. With these two functions, we can solve the problem by using the bitand function provided by oracle. You can convert the IP address and mask to a number through the string-to-IP function, and then obtain the number corresponding to the corresponding network address through bitwise and operation. Then, you can convert the number to a string, the corresponding network address is obtained. For the number of CIDR following/, You can import a table corresponding to the mask and CIDR number. the actual example is as follows: return the 58.247.221.236 SQL code select inttoip (BITAND (dottedQuadToNumber ('58. 247.221.238 '), ottedQuadToNumber ('2017. 255.%252 ') from dual attachment: function for converting a string to a number: www.2cto.com SQL code CREATE OR REPLACE function dottedQuadToNumber (dottedQuad IN VARCHAR2) return NUMBER is Result number; begin Result: = (substr (dottedQuad, 1, (instr (dottedQuad ,'. ', 1, 1)-1) * 256*256*256) + (substr (dottedQuad, instr (dottedQuad ,'. ', 1, 1) + 1, instr (dottedQuad ,'. ', 1, 2)-instr (dottedQuad ,'. ', 1, 1)-1) * 256*256) + (substr (dottedQuad, instr (dottedQuad ,'. ', 1, 2) + 1, instr (dottedQuad ,'. ', 1, 3)-instr (dottedQuad ,'. ', 1, 2)-1) * 256) + (substr (dottedQuad, instr (dottedQuad ,'. ', 1, 3) + 1); return (Result); end dottedQuadToNumber; function for converting numbers to IP addresses: SQL code CREATE OR REPLACE function inttoip (ip_address integer) return varchar2 deterministic is begin return to_char (mod (trunc (ip_address/256/256 /256), 256) | '. '| to_char (mod (trunc (ip_address/256/256), 256) | '. '| to_char (mod (trunc (ip_address/256), 256) | '. '| to_char (mod (ip_address, 256); end;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.