Re: Sorting router interfaces

2010/11/1 Brian Sherwood <bdsher(at)gmail(dot)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(at)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(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/