Display positive result with a plus sign (+) in SQL
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
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 theFORMAT
function) - Microsoft Docs :
SIGN
(Transact-SQL)