Firebird determine if a string is all numbers

Question

I have a VARCHAR field in a Firebird 2.0 table that can contain alphanumeric characters. I need to sort data on this field, sorting all values that contain only numbers as numbers, and sort all other values as 0. For example, if I have four values, "1", "2", "10", "string", I need to sort it as "string", "1", "2", "10". Default sort with string sorts as "1", "10", "2", "string".

I was thinking of CASTing the values to INTEGER, but I'm getting conversion error on strings, which is of course correct. How to work around this?

Answer 1

Create extra column where you store sortable values using your application. Then do sorting based on that column. If you want your numbers to be at the end then insert "ZZZ" (or "ÜÜÜ" or whatever is last character in your language) in front of numbers. Like Format("ZZZ%012d", my_num);

Answer 2

You can use builtin function LPAD:

SELECT
  ...
  <number_field>,
  ...
FROM
  ...
ORDER BY
  LPAD(<numer_field>, 10)