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

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;