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

Source: Internet
Author: User
The problem is as follows: the database stores IP addresses and IP address masks, which need to be converted into the CIDR format, and not only convert the mask into a number corresponding to the CIDR.

The problem is as follows: the database stores IP addresses and IP address masks, which need to be converted into the CIDR format, and not only convert the mask into a number corresponding to the CIDR.

The following problems occur: IP addresses and IP address masks are stored in the database, which need to be converted into the CIDR format and not just the numbers corresponding to the CIDR mask, you need to convert the original IP address to the corresponding network address. For example, if the IP address is 58.247.221.238 and the mask is 255.255.255.252, You need to convert it to 58.247.221.236/30.

Solution: we know that the IP address and mask can be obtained through the bitwise and function. Google, and 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: 58.247.221.236 is returned.

SQL code
Select inttoip (BITAND (dottedQuadToNumber ('58. 247.221.238 '),
OttedQuadToNumber ('1970. 00000000252 ') from dual


Appendix: Functions for converting strings into numbers:

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 ))
* 256x256*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;

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.