Blue Week Special Offer | Brighten your week!

SQL server ordering calculated columns as strings rather than numbers

When I use ‘order by’, SQL Server orders the percentages as shown in the first screenshot. When I try converting to float using the code

convert(float, format(cast(sum(case when HHA_Rank = 7 then 1 else 0 end) as float)/cast(count(*) as float), 'P2')) State_Sustained_Hotspot_Percentage

I get the error message shown in the second screenshot. Please advise.

Can you show what the original table looks like? Specifically around line 148? I’m unable to tell what the matter is from what you shared.

Here is the original table:

Can you show what it looks like around line 148?

I added some descriptive writing; the code formerly on line 148 is now on line 208. The relevant lines of code

and the error message are in two separate screenshots; the line numbers are included for your reference. Please let me know if I can provide anything else.

Sorry for the late response. I made the mistake of assuming the lines referred to the rows in the table rather than the script, so that was a wast of time totally on me.

What’s more, your code should have been enough for me to spot the issue.

You’re doing something like SELECT CONVERT(FLOAT, FORMAT(0.25, 'P2'));, which is equivalent to SELECT CONVERT(FLOAT, '25.00 %'); and CONVERT doesn’t like the format of '25.00 %', hence the error.

I don’t know what you’re trying to accomplish precisely enough for me to provide a solution, but hopefully this helps.

Thank you for your response.

The issue, as you mention, is the % sign; this causes the entries to be read as strings (though in a separate project I did not encounter this issue; SQL read them as numbers despite the presence of the % sign). I fixed the problem by simply removing the % sign and multiplying by 100 to convert to a percentage; the column header indicates that the numbers represent percentages.

1 Like