Обсуждение: Sorting router interfaces

Поиск
Список
Период
Сортировка

Sorting router interfaces

От
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.

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

Re: Sorting router interfaces

От
Filip Rembiałkowski
Дата:
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/

Re: Sorting router interfaces

От
Andreas
Дата:
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   :)