CODEWITHSUNDEEP
sqlsql-servert-sqlnumber-formatting
Hemendr
Hemendr

Reputation: 1068

Number format is giving + sign for negative decimal number

I am trying to format a decimal number with +/- sign with one digit after the decimal. I am using below query

select FORMAT(-0.034, '+0.0;-0.0')

And I am getting +0.0 in the output instead of -0.0

Upvotes: 3

Views: 908

Answers (2)

Zhorov
Zhorov

Reputation: 29983

In a similar situation, using an expression (variable) as a value of the format parameter, was an option:

SELECT FORMAT(
   [Number], 
   CONCAT(
      '+0.0;-0.0;',  
       CASE 
          WHEN [Number] < 0 THEN '-0.0'
          WHEN [Number] > 0 THEN '+0.0'
          ELSE ' 0.0'
       END
   )
) AS [Result]
FROM (VALUES
   (-0.034),
   (0.034),
   (0),
   (0.153),
   (-0.153)
) v ([Number])

Result:

Result
------
-0.0
+0.0
 0.0
+0.2
-0.2

Upvotes: 4

Hemendr
Hemendr

Reputation: 1068

There was a small issue with the solution from @Zhorov as I was getting double negative sign e.g '--0.2' with an input number of '-0.153'. I tried with other formatting option but could not fix this issue so I have created another solution using the CASE expression suggested by @Zhorov.

SELECT [Number],
case 
when [Number] < 0 then '-' + FORMAT([Number],'0.0;0.0') 
when [Number] > 0 then '+' + FORMAT([Number],'0.0;0.0') 
else '0.0' end
as [Correct-Output],
FORMAT(
   [Number], 
   CASE 
      WHEN [Number] < 0 THEN '-0.0'
      WHEN [Number] > 0 THEN '+0.0'
      ELSE ' 0.0'
   END   
) AS [Result1],
FORMAT(
   [Number], 
   CASE 
      WHEN [Number] < 0 THEN '+0.0;-0.0'
      WHEN [Number] > 0 THEN '+0.0;+0.0'
      ELSE ' 0.0'
   END   
) AS [Result2]
FROM (VALUES
   (-0.034),
   (0.034),
   (0),
   (0.153),
   (-0.153)
) v ([Number])

Output enter image description here

Upvotes: 0

Related Questions