CODEWITHSUNDEEP
sqlsql-serverprecision
Aditya Pewekar
Aditya Pewekar

Reputation: 91

Conditional Decimal Place formatting

I have a scenario where I need to display '-' if the value is null and Numeric data if not null. I have created a function which handles this. Below is the clip of the same.

 DECLARE @response varchar(max)  

 set @response=Case when (@data is null) then '-'  
      else STR(@data,25,@roundUp) 
     end  

      RETURN @response

I need response as below

@data=10258.69 and @roundUp=2 then @response=10258.69
@data=10258.00 and @roundUp=2 then @response=10258
@data=10258.695 and @roundUp=2 then @response=10258.70
@data=10258.69485 and @roundUp=2 then @response=10258.69

I tried below clause which works but don't in my query.

select cast(CAST(round(10028.000,3) as decimal(18,5)) as float)

Please suggest something that can solve this. Tried google.

Upvotes: 0

Views: 632

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81970

Late Answer, but If 2012+, another option is Format()

Example

Declare @YourTable table (SomeCol float)
Insert Into @YourTable values
 (10258.69)
,(10258.00)
,(10258.695)
,(10258.69485)
,(null)

Select *
      ,Formatted = IsNull(Format(SomeCol,choose(sign(SomeCol-floor(SomeCol))+1,'0','0.00')),'-')
 From  @YourTable

Returns

SomeCol     Formatted
10258.69    10258.69
10258       10258
10258.695   10258.70
10258.69485 10258.69
NULL        -

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can extend the case:

set @response = (case when (@data is null) then '-'  
                      when (@data = floor(@data)) then str(@data, 25, 0)
                      else STR(@data,25,@roundUp) 
                 end);
return @response;

I would be careful about this, especially if @data is stored as a floating point number. How do you want 11.001 to be represented? 11.0000001?

You might want:

set @response = (case when (@data is null) then '-'  
                      when abs(@data - floor(@data)) < 0.001 then str(@data, 25, 0)
                      else STR(@data,25,@roundUp) 
                 end);
return @response;

Upvotes: 1

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

You can use round and ceiling function like this. I leave upto you, How you want to incorporate it into your code:

Select round(Ceiling(10000*10258.69)/10000,2)
Select round(Ceiling(10000*10258.00)/10000,2)
Select round(Ceiling(10000*10258.695)/10000,2)
Select round(Ceiling(10000*10258.69485)/10000,2)

Upvotes: 0

Related Questions