UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Sorting Text    
Sorting Text

Image:NotifCleanup.gif This page requires general cleanup in formatting or text to better fit the UA Wiki Guidelines

Sorting text can be a problem when the text in question involves numbers. It is important to keep in mind that, when we are speaking and thinking non-technically, we might refer to any text containing numberic digits, as a 'number.' However, when we want to have data sorted in a particular order, we have to be more precise in understanding the data we are trying to work with.

Can you tell by looking at this character 2 whether it is simply text or whether it is a numeric digit. You might think that this is a picky distinction but consider this. If you type 123-4567 into a cell in a spreadsheet, you may think that you have typed a phone number. But if the spreadsheet interprets what you have typed as numeric, it will display the result of subtracting 4567 from 123 or -4444. On the other hand, if the spreadsheet interprets what you have typed as text, it will display 123-4567 (An Excel worksheet will interpret 123-4567 as text unless you type =123-4567.

So, having made the distinction between text and number, the second thing to keep in mind is that numeric text values must have the same number of characters in order to be sorted into a meaningful order. The reason for this is that sorting routines frequently default to processing the text from left to right. Let's look at some text values like 1,2,3,4,5,6,7,8,9,10,11, ... 20, ... 30. If you were to sort the sorte list would look like this:

1 10 11 12 ... 19 2 20 21 ... 3 30

and so on. There aren't too many situations where this would prove to be a useful order in which to review data. Notice how every value with a 1 in the leftmost position comes before any values beginning with 2, for example.

Now, if you have a table with a text field containing numeric data, you won't be get satisfactory results when you sort the table by that field. This example shows a text field as the data was entered.

Image:TextSort.PNG

Everything seems to be ok as far as sort order goes. Howerver, the field is not sorted. It just happens to appear to be in numerical order. Let's add a new record and then sort the field. Just by looking at the first few records, it is easy to see that there's a problem. The new value in the field is 100. Surely that value should sort after 30 but there it is before 11.

Image:TextSortSorted.PNG

One solution is to create a query with a calculated field that produces values with exactly the same number of digits.

Here's what it would look like.

Image:TextSortQuery.PNG

And these are the results:

Image:TextSortResults.PNG


The query uses the LPad function to do the padding.

For an alternate approach, see Gustav's post in this UA thread. [1]

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 6,092 times.  This page was last modified 01:44, 10 February 2012 by Jack Leach. Contributions by Glenn Lloyd  Disclaimers