An other option is to use space-strings with a fix character count and fill in your numbers from right to left, i.e. rightalign your numbers, so that they end in the second and then append your modifier. Your example then look like:
rooms " 1 " "19 " " 2 " "22 " "20C" "15 " " 2A" " 2B" "23 " " 4 " "36A" "12 " "16 " "29 " "36 " With a query like: SELECT room FROM Table1 ORDER BY cast( Left(room,2) as integer),Substr(room,3,1) rooms should ordered appropriate. SQL-Syntax and more can be found at http://hsqldb.org/doc/guide/ch09.html to avoid additional spaces. HTH d > Daniel Kasak schreef: > > Jan Bassez wrote: > >> Is there a way to sort numeric values in a normal text view without > >> converting them the numbers with preceding zero's? > >> > >> Now they are sorted like this: > >> > >> 1 > >> 12 > >> 15 > >> 16 > >> 19 > >> 2 > >> 22 > >> 23 > >> 29 > >> 36 > >> 36A > >> 4 > >> ... > >> > >> > >> The reason I use tekst field is because sometime I need to use for > >> instance: 4A or 4B > > I would split the numeric and strings parts. ie have a numeric field, > > and then an extra field to store your 'modifier' or whatever. Then > > sort by numeric_field, string_field > > > That's an option but I see that more as a workaround. > I also have my classical records in a Base catalog. Many Classical > pieces have Opus number, so I have a field for that. It contains this > content: > op. 14 > op. 45 > op. 125 > > and it has the same sorting problem. > One could say leave the 'op.' > but pieces of Bach have BWV instead of op., also you have WoO (Without > opus) and KV & P for other composers. > > Splitting them up is an idea I dont like too much.... > > Isn't it possible for some kind of combinable field String/numeric field. > or a sorting option wich looks for numeric values in a string field, and > threats them as such? > ..in future versions? > -- Echte DSL-Flatrate dauerhaft für 0,- Euro*! "Feel free" mit GMX DSL! http://www.gmx.net/de/go/dsl --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
