Order by a field containing Numbers and Letters

Question

I need to extract data from an existing Padadox database under Delphi XE2 (yes, i more than 10 years divide them...).

i need to order the result depending on a field (id in the example) containing values such as : '1', '2 a', '100', '1 b', '50 bis'... and get this :

- 1
- 1 b
- 2 a
- 50 bis
- 100

maybe something like that could do it, but those keywords don't exist :

SELECT id, TRIM(TRIM(ALPHA FROM id)) as generated, TRIM(TRIM(NUMBER FROM id)) as generatedbis, etc
FROM "my.db"
WHERE ...
ORDER BY generated, generatedbis

how could i achieve such ordering with paradox... ?

Answer 1

Try this:

SELECT id, CAST('0' + id AS INTEGER) A 
FROM "my.db" 
ORDER BY A, id