Cast as Int only when character is a number on Firebird 2.5

Question

I have a query that I use on a MySQL database that orders the result, casting a char database field to integer when possible (when the char string is numeric), so for example the ORDER BY clause that I use on MySQL is:

ORDER BY
   CASE 
   WHEN CONVERT(charfield, SIGNED INTEGER) IS NOT NULL THEN
        CAST(charfield AS SIGNED INTEGER)  
   ELSE 9999999999 END

Where charfield is a database field character(25).

How can I translate this ORDER BY clause for Firebird 2.5?

Answer 1

You could use SIMILAR TO operator, ie

ORDER BY
   CASE 
      WHEN charfield SIMILAR TO '[0-9]+' THEN CAST(charfield AS INTEGER) 
      ELSE 9999999 
   END

If the field has leading or trailing spaces then you have to use TRIM() function to get rid of those before test, ie

WHEN TRIM(charfield) SIMILAR TO ...

And to allow negative numbers you have to modify the pattern to include -, ie the clause would became

ORDER BY
   CASE 
      WHEN TRIM(charfield) SIMILAR TO '\-?[0-9]+' ESCAPE '\' THEN CAST(charfield AS INTEGER) 
      ELSE 9999999 
   END