How to sort IPv4 and IPv6 addresses within 1 column

Question

IPCIDR                           FLAG_V6
-------------------------------- ----------
58.97.1.12/30                   
58.97.1.16/30                   
58.97.1.96/30                   
58.97.1.100/30                  
58.97.1.116/30                  
2001:fb0:1061:99:0:0:0:0 /64      1 
2001:fb0:1061:9a:0:0:0:0 /64      1 
2001:fb0:1061:9f:0:0:0:0 /64      1 
2001:fb0:1061:a2:0:0:0:0 /64      1 

I want to sort ipv4

 order by (to_number(substr(ipcidr,1,instr(ipcidr,'.')-1))
 , to_number(substr(ipcidr,instr(ipcidr,'.')+1, instr(ipcidr,'.',1,2) - instr(ipcidr,'.') - 1))
 , to_number(substr(ipcidr,instr(ipcidr,'.',1,2)+1, instr(ipcidr,'.',1,3) - instr(ipcidr,'.',1,2) - 1))
 , to_number(substr(ipcidr,instr(ipcidr,'.',1,3)+1,instr(ipcidr,'/')-instr(ipcidr,'.',1,3) - 1))
 , to_number(substr(ipcidr,instr(ipcidr,'/')+1)))

and sort ipv6 in one colum and I have FLAG_V6 column if ipv6 set 1 else null

Answer 1

You can use this function to translate IP address into an integer number:

-- Convert a binary/octal/hex number into a decimal value 
CREATE FUNCTION Base2Dec(BaseString IN VARCHAR2, Base IN PLS_INTEGER DEFAULT 16) RETURN NUMBER DETERMINISTIC IS

    BaseNumber NUMBER := 0;
    HexString CONSTANT CHAR(16) := '0123456789ABCDEF';

BEGIN
    IF BaseString IS NULL THEN
        RETURN NULL;
    ELSIF Base NOT IN (2, 8, 16) THEN 
        RAISE VALUE_ERROR;
    ELSIF Base = 16 THEN
        RETURN TO_NUMBER(BaseString, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    ELSE
        FOR i IN 1..LENGTH(BaseString) LOOP
            BaseNumber := BaseNumber * Base + INSTR(HexString, UPPER(SUBSTR(BaseString, i, 1))) - 1;
        END LOOP;
        RETURN BaseNumber;
    END IF; 
END Base2Dec;

CREATE FUNCTION UncompressIpV6(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
    IpFull VARCHAR2(40);
BEGIN
    IF REGEXP_LIKE(Ip, '::') THEN
        IpFull := REGEXP_REPLACE(REGEXP_REPLACE(Ip, '^::', '0::'), '::$', '::0');
        WHILE REGEXP_COUNT(IpFull, ':') <= 7 LOOP
            IpFull := REGEXP_REPLACE(IpFull, '::', ':0::');
        END LOOP;   
        RETURN REGEXP_REPLACE(IpFull, '::', ':');
    ELSE
        RETURN Ip;
    END IF;
END UncompressIpV6;

CREATE FUNCTION Ip2Decimal(IP IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
    DecimalIp NUMBER := 0;
BEGIN

    IF REGEXP_LIKE(IP, ':') THEN
        SELECT SUM(Base2Dec(REGEXP_SUBSTR(UncompressIpV6(IP), '[[:xdigit:]]+', 1, LEVEL), BASE_HEX) * POWER(65536, 8-LEVEL))
        INTO DecimalIp
        FROM dual 
        CONNECT BY LEVEL <= 8;
        RETURN DecimalIp;       
    ELSE
        SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
        INTO DecimalIp
        FROM dual 
        CONNECT BY LEVEL <= 4;
        RETURN DecimalIp;   
    END IF;

END Ip2Decimal;

It works for both, IPv4 and IPv6. It should be quite obvious to make it only for IPv4, resp. IPv6 working.

You can use it in your ORDER BY clause:

ORDER BY FLAG_V6 NULLS FIRST, Ip2Decimal(IPCIDR)