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

Question

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)

More generally: https://stackoverflow.com/a/8502570/939860 – Erwin Brandstetter Dec 21 '22 at 03:39 — Erwin Brandstetter, Dec 21 '22 at 03:39

score 3 · Accepted Answer · answered Dec 21 '22 at 01:51

3

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

answered Dec 21 '22 at 01:51

Ricky Gummadi

4,559
2
41
67

score 1 · Answer 2 · answered Dec 21 '22 at 05:29

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.

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

2 Answers2