Tuesday, May 18, 2010

varchar IP address sorting

Equivalent of Oracle code with varchar field containing ip (Note: 2.5.6.7 should sort first than 10.3.4.5) ...

SELECT col
  FROM table
 ORDER BY 
(regexp_substr(col, '[^.]+', 1, 1) * 256  * 256  * 256 ) + (regexp_substr(col, '[^.]+', 1, 2) * 256 * 256) + (regexp_substr(col, '[^.]+', 1, 3) * 256 ) + regexp_substr(col, '[^.]+', 1, 4)

...To Postgres:

select x.col
from (values('10.3.4.5'),('2.5.6.7')) as x(col)
order by 
split_part(x.col, '.', 1)::int * 256 * 256 * 256
+ split_part(x.col, '.', 2)::int * 256 * 256 
+ split_part(x.col, '.', 3)::int * 256 
+ split_part(x.col, '.', 3)::int

Postgres has built-in ip address type (inet):

select x.col
from (values('10.3.4.5'),('2.5.6.7')) as x(col)
order by 
x.col::inet

No comments:

Post a Comment