Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
A more accurate sort order Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
25-Aug-03
Category
Algorithm
Language
Delphi 2.x
Views
108
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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")

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC