Mittwoch, 9. März 2016

Oracle - ORDER BY for numeric values in VARCHAR2 field

We have an address list where the housenumber is stored as VARCHAR2. The house number is not always a number. Here a few examples:

---
  21-25   
 54-58, 66-70, 76-78
1
...
10
10a-10b
10b
102
102-104
10-34
...
12 / 14
...
128
1+3
...
2
2 - 10
2, 4, 6
2a-2c
2b / 2c
20

What all data sets have in common is that the first characters are always digits. To order all data sets numerically using the housenumber the following SQL can be used

select strassennr, 
from wfe_sk_einsatzplan 
order by  lpad(regexp_substr(strassennr,'(([0-9]+))'),20);

Which returns a list like:

1
1 - 49
...
1-19
1+3
1
1-7
1
1
1-5
1
...
1
1-7
1-11
1-3
...
1-3
1-7
1
1+3a
2, 4, 6
2
2b / 2c
2
2-12
2-12
2
2-4
2-12
2
2+8
..
2-6, 7-23
2-6c
..
2a-2c
...

REGEXP_SUBSTR extracts the first numeric characters we need for sorting. As the result is still of type VARCHAR2 LPAD makes sure, that they are ordered accordingly (see here).

Alternative: to_number(regexp_substr(strassennr,'(([0-9]+))'));

The report now shows the datasets in an order the user expects:
datasets ordered by house number

Keine Kommentare:

Kommentar veröffentlichen