Alternatives to sign() in sqlite for custom order by

Question

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.

Answer 1

The ifnull function can convert nulls to -1 :

select *, ifnull(referenceid,-1) as reff 
  from table
order by reff desc, name asc;

Answer 2

After a while I managed to come up with this:

SELECT * FROM table
order by length(substr(referenceid,1,1)) desc, name asc;