Alternatives to sign() in sqlite for custom order by

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.

The ifnull function can convert nulls to -1 :

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

After a while I managed to come up with this:

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

This works like the Oracle sign() function:

SELECT
    mynum,
    max(min(mynum, 1), -1) sign
FROM
(
SELECT 2 mynum
UNION ALL
SELECT NULL mynum
UNION ALL
SELECT 0 mynum
UNION ALL
SELECT -2 mynum
);