Sorting Street Numbers

General
1

Hi all,

I have a listbox in which there are columns for Street and Street Numbers. Street Numbers are string type that can be in various formats:

No. 1
No. 11-13
No. 118, 120, 122
No. 62a-64b

the problem is when doing a sort in Street Numbers, 11 or 110 comes before 2 or 20 as the first character 1 in ascii goes before 2, how can I resolve this?

2

Well, you need to process the numbers and cache the numerical value with them.
So find first digits till no digits and use that.

1
11
62
118

Could be the list of numbers.
You may also parse out the letter following the digits to use that for sorting if the numbers are equal.

3

  1. Find a database that does natural sort like Valentina.

  2. The following code is the comparator for sorting text which has numbers at the end like “account-1” and “account-2” and so on:

[code]Public Function Compare(s1 as String, s2 as String) as Integer
// Part of the StringComparator interface.

const NSCaseInsensitiveSearch = 1

dim theRegex as new RegEx
theRegex.searchPattern = “(.*)(\d)+”

'if one of the search texts doesn’t contain a number it doesn’t matter
dim theRegexMatch as RegExMatch = theRegex.search(s1)
if theRegexMatch = nil then
Return NSStringCompareMBS(s1, s2, NSCaseInsensitiveSearch)
else
s1 = theRegexMatch.subExpressionString(1) + Right(“00000” + theRegexMatch.subExpressionString(2), 5)
end if
theRegexMatch = theRegex.search(s2)
if theRegexMatch = nil then
Return NSStringCompareMBS(s1, s2, NSCaseInsensitiveSearch)
else
s2 = theRegexMatch.subExpressionString(1) + Right(“00000” + theRegexMatch.subExpressionString(2), 5)
end if

Return NSStringCompareMBS(s1, s2, NSCaseInsensitiveSearch)
End Function
[/code]

4

Listbox.CompareRows:
@ http://documentation.xojo.com/index.php/ListBox.CompareRows

5

Thanks all. Do you think if I add leading zeros would be a way also? e.g. 0001, 0034a, 0068-0070 … etc

6

yes… but that alters the data inappropriately (unless you are talking about only doing this internally)

my address is 231 Main Street… not 000231 Main Street

And… how many zeros do you add? 3? 4? 10?

7

I would dump the addresses into an in-memory SQLite database with an extra field containing the VAL() of the address, then SELECT the address back again, but ORDER BY valueField.