Here’s a quick way to add brackets around negative numbers in SQL Server when using the FORMAT() function.
The goal here is that brackets are only added to negative values. No brackets are added to positive values or zeros. Also, the brackets replace any minus sign that would otherwise be displayed (in other words, no minus sign is displayed when the brackets are used).
Although formatting is often best left to the presentation layer, there may be cases that dictate a T-SQL solution in SQL Server. In such cases, hopefully this article helps.
Example 1 – Automatic Parentheses
As mentioned, the solutions in this article use the FORMAT() function. This function formats a numeric (or date/time) value, then returns a formatted string representation of that value.
When calling this function, you pass the value to be formatted, and a format string that determines how it will be formatted. You can also provide a third argument to define the locale/culture to use for the output.
Where I’m going with this, is that there are some cases where FORMAT() will automatically surround negative values with brackets, depending on the format string and culture being used.
Here’s an example of formatting a number as a currency using two different “culture” arguments:
SELECT FORMAT(-1.23, 'C', 'en-us') 'en-us', FORMAT(-1.23, 'C', 'en-gb') 'en-gb';
Result:
+---------+---------+ | en-us | en-gb | |---------+---------| | ($1.23) | -£1.23 | +---------+---------+
In this case, it’s a combination of the format string and the culture that determines whether negative values are surrounded by parentheses or not.
The C is a standard numeric format specifier, that formats the number as a currency. When this format specifier is used, the exact output is determined by the culture. This is because different cultures use different conventions for displaying currency amounts. The culture will determine the actual currency symbol to be used, its placement, as well as how negative values are displayed.
If you don’t specify a culture, the language of the current session will be used. This is usually the default language for the user, but it can also be changed with the SET LANGUAGE statement.
Example 2 – Conditional Formatting
If a standard format string doesn’t provide the results you need, you’ll need to use a custom numeric format string instead.
Here’s an example of using a custom numeric format string to enclose the result in parentheses:
SELECT FORMAT(-123, '0; (0)') Result;
Result:
+----------+ | Result | |----------| | (123) | +----------+
To achieve the results we want, the format string uses a section separator to provide conditional formatting.
Conditional formatting is where you specify a different format, depending on a given condition. When using the FORMAT() function, you can use conditional formatting to apply different formatting to a number, depending on whether that number is positive, negative, or zero.
In this context, conditional formatting is made possible by the semicolon (;). This is referred to as the “section separator”. In this case, I only used one semicolon, because I only want two sections (to distinguish between negatives and non-negatives).
When only two sections are included, the first section applies to both positive values and zeros. The second section applies to negative values. You can also add another semicolon to specify another format just for zeros (more on that below).
Example 3 – Compared with Positive and Zero
Here’s another example, this time I include a positive value and zero (just to demonstrate the point more clearly).
SELECT FORMAT(-123, '0; (0)') Negative, FORMAT(123, '0; (0)') Positive, FORMAT(0, '0; (0)') Zero;
Result:
+------------+------------+--------+ | Negative | Positive | Zero | |------------+------------+--------| | (123) | 123 | 0 | +------------+------------+--------+
Example 4 – Alternative Formatting
You aren’t limited to just parentheses. You could use curly braces for example, or square brackets, or almost anything you like.
SELECT
FORMAT(-123, '0; {0}') R1,
FORMAT(-123, '0; [0]') R2,
FORMAT(-123, '0; WARNING! NEGATIVE VALUE!!!') R3;
Result:
+--------+--------+-----------------------------+
| R1 | R2 | R3 |
|--------+--------+-----------------------------|
| {123} | [123] | WARNING! NEGATIVE VALUE!!! |
+--------+--------+-----------------------------+
Example 5 – Three Conditions
As mentioned, you can also add a third condition in order to provide separate formatting for zeros. Here’s a quick example:
SELECT FORMAT(-123, '0; (0); 0 (Zero)') R1, FORMAT(123, '0; (0); 0 (Zero)') R2, FORMAT(0, '0; (0); 0 (Zero)') R3;
Result:
+--------+------+-----------+ | R1 | R2 | R3 | |--------+------+-----------| | (123) | 123 | 0 (Zero) | +--------+------+-----------+
Example 6 – Where’s the Minus Sign?
You might’ve noticed that the minus sign hasn’t even appeared on the negative values. This is because the section separator ignores any preexisting formatting on a value (including any minus signs). This means that, if you’re using conditional formatting, and you do actually want the minus sign on negative values, you’ll need to add it in your format string:
SELECT FORMAT(-123, '0; 0 (Negative); 0 (Zero)') 'Without Minus Sign', FORMAT(-123, '0; -0 (Negative); 0 (Zero)') 'With Minus Sign';
Result:
+----------------------+-------------------+ | Without Minus Sign | With Minus Sign | |----------------------+-------------------| | 123 (Negative) | -123 (Negative) | +----------------------+-------------------+
However, there are some cases where the minus sign remains intact (at least on my system):
SELECT FORMAT(123, '0;; 0 (Zero)') Positive, FORMAT(-123, '0;; 0 (Zero)') Negative;
Result:
+------------+------------+ | Positive | Negative | |------------+------------| | 123 | -123 | +------------+------------+
In this example, negative and positive values share the same section. This is because I added a section separator for negative values but left it empty. In this case, the format string in the first section applies to both positive and negative values.
As mentioned, in this case, the minus sign remains intact for negative values.