Using SQL CASE statement but returning same column values

Question

I'm a novice in SQL & have the following issue:

I have the following column in a table called performances:

numbers
-------
13.80
4.9
32.1
0.00
12
0.00

I would like to select this table, but where 0.00, I want to return -. So after the select it should be:

numbers
-------
13.80
4.9
32.1
-
12
-

I've tried using a case as follows:

select
    numbers, 
    case numbers
        when 0.00 then '-' 
    end as numbers
from 
    Performance

I know this incorrect & another spanner in the works is that the numbers column datatype is decimal(18, 2)

Can someone please assist me with this select? Like I mentioned, I'm still new to SQL, but I'm thinking CASE is not the right option for this.

THANK YOU

Why do you want to do this? This smells like you're asking for a solution to the wrong problem. Have you considered using NULL values instead of 0.00, if 0.00 means there are no measurements? Do you want '-' when presenting the data? If so, you should not do that in your database layer, but in your application layer. — Fredrik E
– Fredrik E, Commented Dec 10, 2012 at 8:08
YES - It is only for presenting the data, no change is made to the DB — user1890948
– user1890948, Commented Dec 10, 2012 at 8:55
So the proper way to do it would be to check the value in your program, and if it is equal to 0.0 print '-' instead. Consider also using NULL instead of 0.0 if there's no measurement. — Fredrik E
– Fredrik E, Commented Dec 10, 2012 at 13:26

Mikael Eriksson · Accepted Answer · 2012-12-10 08:03:47Z

4

You need to cast your numbers to a varchar(19) (18 + decimal separator).

select case numbers 
         when 0 
         then '-' 
         else cast(numbers as varchar(19)) 
       end as numbers

answered Dec 10, 2012 at 8:03

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1