CODEWITHSUNDEEP
sqloracle-database
Mark Harrison
Mark Harrison

Reputation: 304554

How can I sort IP addresses stored as varchar2 in Oracle?

How can I sort numerical IP addresses stored as varchar2? I would the addresses to sort in "natural" numerical order rather than lexicographical order (e.g. '1.1.1.9' before '1.1.1.10').

Upvotes: 0

Views: 634

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59493

You could create a function which translate it to a decimal number:

CREATE OR REPLACE FUNCTION Ip2Decimal(IP IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS
    DecimalIp INTEGER := 0;
BEGIN
    IF Ip IS NULL THEN 
        RETURN NULL; 
    END IF;
    FOR i IN 1..4 LOOP
        DecimalIp := DecimalIp + REGEXP_SUBSTR(IP, '\d+', 1, i) * 256**(4-i);
    END LOOP;
    RETURN DecimalIp;
END Ip2Decimal;

Then sort by this function value. A function-based-index should be usefull in this case.

Upvotes: 0

Mark Harrison
Mark Harrison

Reputation: 304554

You can use regexp_replace to pad each octet with leading zeroes so that 1.1.1.9 will be transformed to 001.001.001.009. 

create table t (a varchar2(16));
insert into t values('10.1.1.1');
insert into t values('10.1.1.9');
insert into t values('10.1.1.10');

select * from t order by regexp_replace(regexp_replace(a,'(\d+)','00\1'),'0*(\d{3})','\1');

a
-
10.1.1.1
10.1.1.9
10.1.1.10

If performance is a consideration you can consider creating a function-based index or converting the IP address to a number before storing it.

create index ix on t (regexp_replace(regexp_replace(a,'(\d+)','00\1'),'0*(\d{3})','\1'));

Upvotes: 1

Related Questions