SQL - How to sort numbers in a VARCHAR column with empty strings as entries-CodePudding

I have a postgres column which is like so:

It only has numbers or empty string.

I want to be able to sort the numbers by the numbers but as I go to cast the column to a float, it will give me the following error:

ERROR:  invalid input syntax for type double precision: ""

Is there a way I can do this sort, and having the empty strings be treated as 0?

This is my query that's failing:

SELECT C.content
FROM row R 
LEFT JOIN cell C ON C.row_id = R.row_id 
WHERE R.database_id = 'd1c39d3a-0205-4ee3-b0e3-89eda54c8ad2' 
AND C.column_id = '57833374-8b2f-43f3-bdf5-369efcfedeed'
ORDER BY cast(C.content as float)

CodePudding user response：

when its an empty string you need to either treat it as null or 0 and then it will work, try putting a case statement like so in the order by

ORDER BY 
    case when C.content = '' then 0
         else cast(C.content as float)
    end

CodePudding user response：

If it's sure this column will never have negative values, a simple option is just adding a leading zero.

If the column is NULL or has an empty string, it will be sorted as 0.

Otherwise, the value will be sorted as it is because adding a leading zero doesn't change anything.

SELECT yourcolumn 
FROM yourtable
ORDER BY CAST(CONCAT('0',yourcolumn) AS FLOAT);

If negative values can appear, this would fail, so I would then use CASE WHEN.

But I propose to also take 0 for NULL values, not only for empty strings:

SELECT yourcolumn 
FROM yourtable
ORDER BY 
CASE WHEN yourcolumn = '' OR yourcolumn IS NULL 
  THEN 0
  ELSE CAST(yourcolumn AS FLOAT)
END;

Otherwise, NULL values would be sorted as highest number which is likely not intended.

And yes, I know you wrote there are numbers and empy strings only in your table, but maybe this can change (unless the column is not nullable). So adding this condition doesn't hurt.