I somehow didn’t manage to run the query with CAST. I was always getting Error Code: 1064 near "DECIMAL" (or other numeric type that I chose). So, I found another way to sort varchar as numbers:
SELECT *
FROM mytable
ORDER BY ABS(mycol)
A bit simpler and works in my case.