Thread: Sorting router interfaces
I am trying to sort router interface names.
The problem is that I am doing a text sort and need to do a numerical sort.
I want the interfaces to be in numerical order:
GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
GigabitEthernet1/0/3 | 1/0/3 | {1,0,3}
etc.....
What I get instead is the following text ordering:
GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
GigabitEthernet1/0/10 | 1/0/10 | {1,0,10}
GigabitEthernet1/0/11 | 1/0/11 | {1,0,11}
GigabitEthernet1/0/12 | 1/0/12 | {1,0,12}
GigabitEthernet1/0/13 | 1/0/13 | {1,0,13}
GigabitEthernet1/0/14 | 1/0/14 | {1,0,14}
GigabitEthernet1/0/15 | 1/0/15 | {1,0,15}
GigabitEthernet1/0/16 | 1/0/16 | {1,0,16}
GigabitEthernet1/0/17 | 1/0/17 | {1,0,17}
GigabitEthernet1/0/18 | 1/0/18 | {1,0,18}
GigabitEthernet1/0/19 | 1/0/19 | {1,0,19}
GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
GigabitEthernet1/0/20 | 1/0/20 | {1,0,20}
GigabitEthernet1/0/21 | 1/0/21 | {1,0,21}
GigabitEthernet1/0/22 | 1/0/22 | {1,0,22}
GigabitEthernet1/0/23 | 1/0/23 | {1,0,23}
GigabitEthernet1/0/24 | 1/0/24 | {1,0,24}
GigabitEthernet1/0/25 | 1/0/25 | {1,0,25}
GigabitEthernet1/0/26 | 1/0/26 | {1,0,26}
GigabitEthernet1/0/27 | 1/0/27 | {1,0,27}
GigabitEthernet1/0/28 | 1/0/28 | {1,0,28}
GigabitEthernet1/0/29 | 1/0/29 | {1,0,29}
GigabitEthernet1/0/3 | 1/0/3 | {1,0,3}
GigabitEthernet1/0/30 | 1/0/30 | {1,0,30}
GigabitEthernet1/0/31 | 1/0/31 | {1,0,31}
GigabitEthernet1/0/32 | 1/0/32 | {1,0,32}
GigabitEthernet1/0/33 | 1/0/33 | {1,0,33}
FYI: I also have entries like the following:
lc-5/2/0.32769 | 5/2/0.32769 | {5,2,0.32769}
irb.5 | .5 | {.5}
irb.51 | .51 | {.51}
irb.52 | .52 | {.52}
ae6 | 6 | {6}
ae7 | 7 | {7}
lo0.0 | 0.0 | {0.0}
Vlan710 | 710 | {710}
Vlan760 | 760 | {760}
Vlan910 | 910 | {910}
Vlan910 | 910 | {910}
gre | | {""}
tap | | {""}
dsc | | {""}
The above listings are produced with the following:
SELECT
interface,
regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
regexp_split_to_array(regexp_replace(interface,'[A-Za-z
-]+','','g'),E'/') as "sort_col"
FROM all_ports
ORDER BY devicename,sort_col
I have tried to break out the interface number to a separate array
column to sort on and was hoping to cast the array to a float[], but
no luck:
SELECT
interface,
regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
regexp_split_to_array(regexp_replace(interface,'[A-Za-z
-]+','','g'),E'/')::float as "sort_col"
FROM all_ports
psql:-:15: ERROR: cannot cast type text[] to double precision
LINE 5: ...gexp_replace(interface,'[A-Za-z -]+','','g'),E'/')::float as...
Can anyone suggest a better approach or help with this approach?
Thanks
2010/11/1 Brian Sherwood <bdsher@gmail.com>:
> I am trying to sort router interface names.
> The problem is that I am doing a text sort and need to do a numerical sort.
>
> I want the interfaces to be in numerical order:
>
> GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
> GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
> GigabitEthernet1/0/3 | 1/0/3 | {1,0,3}
> etc.....
>
>
> What I get instead is the following text ordering:
>
> GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
> GigabitEthernet1/0/10 | 1/0/10 | {1,0,10}
> GigabitEthernet1/0/11 | 1/0/11 | {1,0,11}
> GigabitEthernet1/0/12 | 1/0/12 | {1,0,12}
> GigabitEthernet1/0/13 | 1/0/13 | {1,0,13}
> GigabitEthernet1/0/14 | 1/0/14 | {1,0,14}
> GigabitEthernet1/0/15 | 1/0/15 | {1,0,15}
> GigabitEthernet1/0/16 | 1/0/16 | {1,0,16}
> GigabitEthernet1/0/17 | 1/0/17 | {1,0,17}
> GigabitEthernet1/0/18 | 1/0/18 | {1,0,18}
> GigabitEthernet1/0/19 | 1/0/19 | {1,0,19}
> GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
> GigabitEthernet1/0/20 | 1/0/20 | {1,0,20}
> GigabitEthernet1/0/21 | 1/0/21 | {1,0,21}
> GigabitEthernet1/0/22 | 1/0/22 | {1,0,22}
> GigabitEthernet1/0/23 | 1/0/23 | {1,0,23}
> GigabitEthernet1/0/24 | 1/0/24 | {1,0,24}
> GigabitEthernet1/0/25 | 1/0/25 | {1,0,25}
> GigabitEthernet1/0/26 | 1/0/26 | {1,0,26}
> GigabitEthernet1/0/27 | 1/0/27 | {1,0,27}
> GigabitEthernet1/0/28 | 1/0/28 | {1,0,28}
> GigabitEthernet1/0/29 | 1/0/29 | {1,0,29}
> GigabitEthernet1/0/3 | 1/0/3 | {1,0,3}
> GigabitEthernet1/0/30 | 1/0/30 | {1,0,30}
> GigabitEthernet1/0/31 | 1/0/31 | {1,0,31}
> GigabitEthernet1/0/32 | 1/0/32 | {1,0,32}
> GigabitEthernet1/0/33 | 1/0/33 | {1,0,33}
>
>
> FYI: I also have entries like the following:
> lc-5/2/0.32769 | 5/2/0.32769 | {5,2,0.32769}
> irb.5 | .5 | {.5}
> irb.51 | .51 | {.51}
> irb.52 | .52 | {.52}
> ae6 | 6 | {6}
> ae7 | 7 | {7}
> lo0.0 | 0.0 | {0.0}
> Vlan710 | 710 | {710}
> Vlan760 | 760 | {760}
> Vlan910 | 910 | {910}
> Vlan910 | 910 | {910}
> gre | | {""}
> tap | | {""}
> dsc | | {""}
>
>
> The above listings are produced with the following:
>
> SELECT
> interface,
> regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
> regexp_split_to_array(regexp_replace(interface,'[A-Za-z
> -]+','','g'),E'/') as "sort_col"
> FROM all_ports
> ORDER BY devicename,sort_col
>
>
> I have tried to break out the interface number to a separate array
> column to sort on and was hoping to cast the array to a float[], but
> no luck:
>
> SELECT
> interface,
> regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
> regexp_split_to_array(regexp_replace(interface,'[A-Za-z
> -]+','','g'),E'/')::float as "sort_col"
> FROM all_ports
>
> psql:-:15: ERROR: cannot cast type text[] to double precision
> LINE 5: ...gexp_replace(interface,'[A-Za-z -]+','','g'),E'/')::float as...
>
>
>
> Can anyone suggest a better approach or help with this approach?
good approach, jus needed to be generalized...
filip@filip=# SELECT interface,
regexp_replace( interface, '[0-9].*', '' ) as before_1st_digit,
regexp_replace( interface, '^[^0-9]+', '' ) as from_first_digit,
CASE WHEN interface ~ '[0-9]' THEN regexp_split_to_array( regexp_replace( interface, '^[^0-9]+', ''
), '[^0-9]+' )::int[]
ELSE array[-1] END as nums
FROM interfaces
ORDER BY 2,4;
interface | before_1st_digit | from_first_digit | nums
-----------------------+------------------+------------------+----------eth0 | eth | 0
| {0}eth0/0 | eth | 0/0 | {0,0}eth0/1 | eth
| 0/1 | {0,1}eth0/10 | eth | 0/10 | {0,10}eth1
|eth | 1 | {1}GigabitEthernet1/0/2 | GigabitEthernet | 1/0/2 |
{1,0,2}GigabitEthernet1/0/20| GigabitEthernet | 1/0/20 | {1,0,20}irb.5 | irb. |
5 | {5}irb.51 | irb. | 51 | {51}tun | tun
| | {-1}tun0 | tun | 0 | {0}Vlan72
|Vlan | 72 | {72}Vlan710 | Vlan | 710 | {710}
(13 rows)
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
Am 01.11.2010 13:15, schrieb Brian Sherwood:
> I am trying to sort router interface names.
> The problem is that I am doing a text sort and need to do a numerical sort.
>
>
> What I get instead is the following text ordering:
>
> GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
> GigabitEthernet1/0/10 | 1/0/10 | {1,0,10}
> GigabitEthernet1/0/11 | 1/0/11 | {1,0,11}
> GigabitEthernet1/0/12 | 1/0/12 | {1,0,12}
> GigabitEthernet1/0/13 | 1/0/13 | {1,0,13}
This was the easy part.
Suppose those lines above were the input table "interfaces" and the
columns were called c1, c2, c3.
Since c3 is allready an array you could do this:
select *
from interfaces
order by (c3::integer[])[1], (c3::integer[])[2], (c3::integer[])[3]
Records of this type
> lc-5/2/0.32769 | 5/2/0.32769 | {5,2,0.32769}
work with:
order by (c3::float[])[1], (c3::float[])[2], (c3::float[])[3]
Now you "just" need to identify those records which wont produce such
nice numerical arrays.
Then split the two sets up, sort them in separate selects, add a set_nr
and a row_number() as row_nr.
Then UNION both sets together again
AND eventually do an ORDER BY set_nr, row_nr
and you are allready done.
OK, that would be just an idea :)