Re: Sorting router interfaces - Mailing list pgsql-sql

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/