I would like to be able to specify an ORDER BY which will give me the same sort of ordering as produced by my file browser, where embedded numbers in a string with the same prefix are ordered numerically.

For example: if I have strings 'x1','foo1','x2','foo10','foo5', a normal text sort will give 'foo1','foo10','foo5','x1','x2' (that is, 'foo10' will come before 'foo5'). I would like to be able to produce the order 'foo1','foo5','foo10','x1','x2' instead.

I could do this in Java by defining a custom comparator, and then import this for use in Derby with CREATE FUNCTION but how would I then use it in an ORDER BY clause to give me the desired sort order?

Thanks for any tips,
John English

Reply via email to