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