Display positive result with a plus sign (+) in SQL

Question

I have the following query:

    SELECT 
      CONVERT(DECIMAL(11,1),SUM(Column/1000*-1)) AS NAME,
    FROM
      Table

The reason i have "/1000*-1" is that I would like the results to be displayed in units of thousands and inverted (negative values as positive and vice versa) with only one decimal place.

How can I get the positive values have a plus sign (+) in front of them just like the negative values have a dash sign (-) ?

Answer 1

You can use semicolon-separated multi-part strings with the FORMAT function (kind of like you would with custom number formats in Microsoft Excel).

A number format can have up to three sections of formatting code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, and zero values, in that order:

<POSITIVE>;<NEGATIVE>;<ZERO>

example:

FORMAT(@YourValue,'+0.0;-0.0')

^{(Adapted from this)}

---

I usually also hide zeros when displaying +/- signs so I use formatting string: '+0;-0;'''

SELECT FORMAT(+5,'+0;-0;''') --returns: +5
SELECT FORMAT(-5,'+0;-0;''') --returns: -5
SELECT FORMAT(-5,'+0;-0;''') --returns: <empty string>

---

To display zero's as well you could use formatting string: '+0;-0;0'

---

Applies to: tsql, azure-sql-database, sql-server-2012, sql-server-2014, sql-server-2016

---

More Information:

• Microsoft Docs : FORMAT (Transact-SQL)
• Microsoft Docs : Formatting Types
  ^{(Doc is for .net but also applies to the FORMAT function)}
• Microsoft Docs : SIGN (Transact-SQL)

Answer 2

SELECT 
  case 
     when CONVERT(DECIMAL(11,1),SUM(Column/1000*-1)) >= 0 
     then concat('+', CONVERT(DECIMAL(11,1),SUM(Column/1000*-1))) 
     else CONVERT(DECIMAL(11,1),SUM(Column/1000*-1)) 
  end AS NAME
FROM Table