I have a string column which contains some numeric fields, but a lot are 0, empty string or null. The rest are numbers having different range, all positive.

I tried to create a custom order by. The order would be done by two fields. First I would like to order the fields that have this number >0 and then sort by name.

So something this would work:

select * from table
order by sign(referenceid) desc, name asc;

But Sqlite lacks the sign() -1/0/1 function, and I am on Android and I can't create user defined functions. What other options I have to get this sort done.