Perhaps one of the lesser-known features of the FORMAT() function in SQL Server is one that enables you to apply conditional formatting to a number.
It’s more of a .NET feature than it is a SQL Server (or T-SQL) feature, but SQL Server/T-SQL supports it all the same, allowing you to take full advantage of the ability to apply conditional formatting to numbers.
It all comes down to the format string you pass to the FORMAT() function.
You can pass a format string that specifies how the number should be formatted, depending on whether it’s positive, negative, or zero.
Just to be clear, I’m not talking about formatting the number with colors or fonts etc. I’m solely talking about the number formatting that you would normally use the FORMAT() function for (such as adding thousands separators, percentage signs, decimal points, etc).
Also, this conditional formatting is quite limited – only three conditions can be tested for (positive, negative, or zero). However, you can also apply the same formatting to two conditions at once if required.
In any case, here’s how use the FORMAT() function to apply conditional formatting to a number in SQL Server.
Introducing ; – The Section Separator
.NET defines the semicolon (;) as one of its custom numeric format specifiers, referred to as the section separator.
The section separator is a conditional format specifier that defines sections with separate format strings for positive, negative, and zero numbers. This allows you to apply different formatting to a number depending on whether its value is positive, negative, or zero.
A custom numeric format string can contain up to three sections separated by semicolons. These are as follows:
- One section: No conditional formatting applies in this case. The format string applies to all values. No section separator is needed (because there is only one section). No doubt, this is the most common form of format string.
- Two sections: The first section applies to positive values and zeros, and the second section applies to negative values.
If the number to be formatted is negative, but becomes zero after rounding according to the format in the second section, the resulting zero is formatted according to the first section.
- Three sections: The first section applies to positive values, the second section applies to negative values, and the third section applies to zeros.
The second section can be left empty (by having nothing between the semicolons), in which case the first section applies to all nonzero values.
If the number to be formatted is nonzero, but becomes zero after rounding according to the format in the first or second section, the resulting zero is formatted according to the third section.
Note that negative values are always displayed without a minus sign when section separators are used (although there are exceptions, as you’ll see later). If you want the final formatted value to have a minus sign, you’ll need to explicitly include the minus sign as part of the custom format string. This also applies to any other preexisting formatting associated with a number.
Example 1 – One Section (no conditional formatting)
Here’s a typical numeric format string consisting of one section. No section separators are used, and therefore no conditional formatting applies.
Code:
SELECT FORMAT(123, '0 (Number)') Positive, FORMAT(-123, '0 (Number)') Negative, FORMAT(0, '0 (Number)') Zero;
Result:
+--------------+---------------+------------+ | Positive | Negative | Zero | |--------------+---------------+------------| | 123 (Number) | -123 (Number) | 0 (Number) | +--------------+---------------+------------+
Notice that the minus sign remains intact. This would’ve been removed if we’d used section separators.
Example 2 – Two Sections (conditional formatting)
Here’s where the conditional formatting starts.
In this example, we have two sections (separated by one section separator). The section to the left of the separator only applies to values that are either positive or zero. The section to the right only applies to negative values.
Code:
SELECT FORMAT(123, '0 (Positive or Zero); 0 (Negative)') Result;
Result:
+------------------------+ | Result | |------------------------| | 123 (Positive or Zero) | +------------------------+
In this case the number was positive, so the first section was used to format it.
Example 3 – Two Sections (same format string, different values)
In the next example, the same format string is applied to different values (positive, negative, and zero).
Code:
SELECT FORMAT(123, '0 (Positive or Zero); 0 (Negative)') Positive, FORMAT(-123, '0 (Positive or Zero); 0 (Negative)') Negative, FORMAT(0, '0 (Positive or Zero); 0 (Negative)') Zero;
Result:
+------------------------+-----------------+----------------------+ | Positive | Negative | Zero | |------------------------+-----------------+----------------------| | 123 (Positive or Zero) | 123 (Negative) | 0 (Positive or Zero) | +------------------------+-----------------+----------------------+
So this example demonstrates the true benefit of section separators – that we can get a different outcome depending on the value.
Example 4 – Two Sections with Rounding
When using two sections, any negative values that are rounded to zero are formatted under the first format string.
Code:
SELECT FORMAT(0.1, '0 (Positive or Zero); 0 (Negative)') Positive, FORMAT(-0.1, '0 (Positive or Zero); 0 (Negative)') Negative;
Result:
+----------------------+----------------------+ | Positive | Negative | |----------------------+----------------------| | 0 (Positive or Zero) | 0 (Positive or Zero) | +----------------------+----------------------+
Example 5 – Three Sections (basic usage)
Here’s a basic example of specifying three sections. We use two section separators to achieve this.
Code:
SELECT FORMAT(123, '0 (Positive); 0 (Negative); 0 (Zero)') Result;
Result:
+----------------+ | Result | |----------------| | 123 (Positive) | +----------------+
In this case, the number was a positive value, so it was formatted under the first section.
Example 6 – Three Sections (same format string, different values)
This example demonstrates the various results we could get from the previous example, depending on the input value.
Here, the same format string is applied to different values. I also assign the format string to a variable, but this is just to make it easier to read.
DECLARE @formatstring varchar(35); SET @formatstring = '0 (Positive); 0 (Negative); 0 (Zero)'; SELECT FORMAT(123, @formatstring) 'Positive', FORMAT(-123, @formatstring) 'Negative', FORMAT(0, @formatstring) 'Zero', FORMAT(0.123, @formatstring) 'Rounded to Zero';
Result:
+----------------+-----------------+----------+-------------------+ | Positive | Negative | Zero | Rounded to Zero | |----------------+-----------------+----------+-------------------| | 123 (Positive) | 123 (Negative) | 0 (Zero | 0 (Zero | +----------------+-----------------+----------+-------------------+
Example 7 – Three Sections (including an empty one)
If you leave the second format string empty, the first section applies to all nonzero values. To leave it empty, just leave nothing between the semicolons.
Code:
SELECT FORMAT(123, '0 (Nonzero);; 0 (Zero)') 'Positive', FORMAT(-123, '0 (Nonzero);; 0 (Zero)') 'Negative', FORMAT(0, '0 (Nonzero);; 0 (Zero)') 'Zero', FORMAT(0.123, '0 (Nonzero);; 0 (Zero)') 'Rounded to Zero';
Result:
+---------------+----------------+-----------+-------------------+ | Positive | Negative | Zero | Rounded to Zero | |---------------+----------------+-----------+-------------------| | 123 (Nonzero) | -123 (Nonzero) | 0 (Zero) | 0 (Zero) | +---------------+----------------+-----------+-------------------+
Interestingly, in this case the minus sign for the negative value is left intact.
Example 8 – The Minus Sign
As mentioned, the section separator ignores any preexisting formatting associated with the number. This includes any minus sign for negative values (although the previous example appears to be an exception to this).
If you want to include the minus sign, you’ll need to explicitly add that to your format string. Example below.
Code:
SELECT FORMAT(-123, '0 (P); 0 (N); 0 (Z)') 'Without minus sign', FORMAT(-123, '0 (P); -0 (N); 0 (Z)') 'With minus sign';
Result:
+----------------------+-------------------+ | Without minus sign | With minus sign | |----------------------+-------------------| | 123 (N) | -123 (N) | +----------------------+-------------------+
As pointed out, the previous example seems to be an exception to this, so something to bear in mind. Here’s what happens if I add a minus sign to the format string for the negative value in the previous example:
SELECT FORMAT(-123, '-0 (Nonzero);; 0 (Zero)') Result;
Result:
+-----------------+ | Result | |-----------------| | --123 (Nonzero) | +-----------------+