I have a query that i use on MySQL database that order 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
Query clause for Firebird 2.5 ?
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