Author: Duncan Parsons
Sorting Addresses is a pain at the best of times, especially when a client supplies
bad data (You may define clear fields in your DB, but when the data comes in, does
it fit easily??)
This attempts to resolve this issue
Answer:
1 unit AddrSortOrder;
2
3 {The custom sort order is used to deal with the fact that the
4 house and flat numbers are sorted as strings. They are stored as
5 strings to allow things like '150-175' as a house number, or '3a',
6 or perhaps even simply a flat 'A'.
7 The need for a custom sort order is caused by the fact that with an
8 ordinary ASCII sort order '4' will appear after '30'. This is not
9 desirable behaviour.
10
11 This approach to fix this problem is to look for the first number
12 in the string (if there is one) and then use this as some kind of
13 primary sort order. The rest of the sorting will then be done on
14 the remaining characters (with preceding and trailing spaces
15 stripped out), based on the ASCII value of their upper-
16 case varients. Potential problems caused by this approach include
17 (but are not limited to) the use of accented characters will
18 possibly cause strange orderings and furthermore, if there is a block
19 of flats with three floors A, B, C for example then supposing the
20 flats on those floors are A1, A2, A3, B1, B2, B3 then the ordering
21 of records will not be ideal - this approach will sort them as
22 A1, B1, A2, B2, A3, B3. This behaviour is regrettable, but
23 acceptable - we cannot tell that it is not flat A on floor 1 for
24 example. It's unlikely that we will be able to find a sort order
25 that always produces ideal results.
26 Some examples of sorted lists (not all ideal):
27 EXAMPLE 1 EXAMPLE 2 EXAMPLE 3
28 Flat 1 1 A
29 Flat 2 -2 B
30 3 2-4 C
31 3B 3a 1
32 Flat 3A 5 2
33 }
34
35 interface
36
37 uses SysUtils;
38
39 function CalcSortIndex(NumStr: string): double;
40
41 implementation
42
43 function CalcSortIndex(NumStr: string): double;
44 var
45 strlength, i, j, tmp: integer;
46 found: boolean;
47 numpart, strpart, divisor: double;
48 choppedstr: string;
49 begin
50 //This function will return the sort index value for the string passed
51
52 strlength := length(NumStr);
53 if strlength = 0 then
54 begin
55 result := 0;
56 exit;
57 end;
58
59 found := false;
60
61 //split the string into a 'number' and a 'string' part..
62
63 //initialise
64 choppedstr := numstr;
65 numpart := 0;
66
67 //Locate the first digit (if there)
68 for i := 1 to strlength do
69 begin
70 if numstr[i] in ['0'..'9'] then
71 begin
72 found := true; //First digit found!!
73 break;
74 end;
75 end; //for i..
76
77 if found then
78 begin
79 //now get the to the end of the digits..
80 found := false;
81 for j := i to strlength do
82 begin
83 if not (numstr[j] in ['0'..'9']) then
84 begin
85 found := true; //end of digits found
86 break;
87 end;
88 end; //for j..
89
90 //Separate out the string parts
91 if found then
92 begin
93 //Number was embedded..
94 val(copy(numstr, i, j - i), numpart, tmp);
95 Delete(choppedstr, i, j - i);
96 end
97 else
98 begin
99 //Number went to the end of the string
100 val(copy(numstr, i, strlength), numpart, tmp);
101 Delete(choppedstr, i, strlength);
102 end;
103 end;
104
105 choppedstr := Uppercase(trim(choppedstr));
106 strlength := length(choppedstr);
107
108 //evaluate a number for the remaining part of the string
109 strpart := 0;
110 divisor := 1;
111
112 for i := 1 to strlength do
113 begin
114 divisor := divisor / 256;
115 //convert from Char to single using a variant conversion
116 strpart := strpart + (ord(choppedstr[i]) * divisor);
117 end;
118
119 //All done, return the value
120 result := numpart + strpart;
121 end;
122
123 end.
NB a version of this Algorithm for MSSQL7 is also posted (Title "Towards a more accurate sort order in MSSQL7")
|
