views:

45

answers:

2

I've got a hibernate query that returns a list of objects and I want to order by a title. This is a user maintained field and some of our customers like prefixing their titles with numbers, this isn't something I can control. The data is something like this:

 - 1 first thing
 - 2 second thing
 - 5 fifth thing
 - 10 tenth thing
 - 20 twentieth thing
 - A thing with no number

A traditional

.AddOrder(Order.Asc("Name"))

Is resulting in a textual sort:

 - 1 first thing
 - 10 tenth thing
 - 2 second thing
 - 20 twentieth thing
 - 5 fifth thing
 - A thing with no number

This is correct as this is a nvarchar field but is there any way I can get the numbers to sort as well?

There seem to be several workarounds involving prefixing all fields with leading 0's and such like but do any of these work through NHibernate?

This application runs interchangeably on Oracle and MsSQL.

Cheers,

Matt

A: 

Add additional field (e.g. named Name_Number ) to object defenition and query on server side.

For Oracle select in this field to_number(Name) as Name_Number .

For For MS SQL select in this field cast(Name as numeric) as Name_Number .

Then sort on client side as .AddOrder(Order.Asc("Name_Number"))

P.S. I am not sure, because don't have enough experience with hibernate/nhibernate and hate ORM at all :)

ThinkJet
Unfortunately this won't work as the number is the field isn't actually a separate piece of data, its just extra text that some users add to give precedence to the item to make it appear earlier in the ordering.
Matt Sharpe
A: 

You might consider creating a custom sort order implementation that allows you to specify the collation on the column to achieve your desired results. Something along the lines of:

ORDER BY Name COLLATE Latin1_General_BIN ASC

But with an appropriate collation (I don't believe Latin1_General_BIN is what you need specifically)

DanP
Matt Sharpe
Just tried to vote you up as this answer looks correct but I don't have enough rep. I've marked it as the answer instead.
Matt Sharpe