Let's sort the following strings: a, A, b, B, 1, 2, 10 The ASCIIbetical order is as follows: 1, 10, 2, A, B, a, b The natural sorting, for most human beings, is as follows: 1, 2, 10, a, A, b, B
Sorting strings in most programming languages will result in the ASCIIbetical result, and Jeff wonders if a more human-friendly natural sort option should be built into mainstream programming languages. What about XQuery? We're talking here about collations, and XQuery has built-in support for collations.
The default collation in XQuery is the Unicode Codepoint collation. For example,
[cc lang="xquery"]for $s in ("a", "A", "b", "B", "1", "2", "10") order by $s return $s[/cc]
yields the following result: 1, 10, 2, A, B, a, b.
XQuery implementation are allowed to used a different default collation. With DataDirect XQuery, the default collation is based on the locale of your Java Virtual Machine. The query above will result in: 1, 10, 2, a, A, b, B. That's already better, 'a' and 'A' are sorted before 'b' and 'B'. By the way, using the locale implies that on a German system for example, characters like umlaut will collate as a German would expect.
But we're not yet there, the numbers are still not naturally sorted. You can achieve this with DataDirect XQuery, by explicitly overriding the default collation and specify the alphanumeric option. As shown in the next query,[cc lang="xquery"]declare default collation "http://www.datadirect.com/xquery/collation?alphanumeric=yes"; for $s in ("a", "A", "b", "B", "1", "2", "10") order by $s return $s[/cc] And we get the desired result: 1, 2, 10, a, A, b, B.
Want more tips and tricks?
digg_skin = 'compact'; digg_url = 'hhttp://www.xml-connection.com/2007/12/natural-sorting-in-xquery.html';
View all posts from Marc Van Cappellen on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.Learn More
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
You have the right to request deletion of your Personal Information at any time.
You can also ask us not to pass your Personal Information to third parties here: Do Not Sell My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.