From: | Brian Sherwood <bdsher(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Sorting router interfaces |
Date: | 2010-11-01 12:15:24 |
Message-ID: | AANLkTinfjpHjk7thZ1e6=RtPSY6n8wDxR9spe5bHJcWX@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2010-11-01 21:37:54 | Re: Sorting router interfaces |
Previous Message | Rob Sargent | 2010-10-31 23:07:32 | Re: large xml database |