2010/11/1 Brian Sherwood <bds...@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...
fi...@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.rembialkow...@gmail.com
http://filip.rembialkowski.net/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
