One NULL Turns the Whole Concatenation Into NULL
You build a display label by concatenating a few product columns with +. Name, a dash, the SKU, maybe a size in parentheses. It looks right for every product you spot check. Then a batch of labels comes back blank, no name and no SKU, and the only thing those products have in common is that one of the columns you concatenated is NULL.
This is Part 6 of The UNKNOWN Problem, a series on NULL traps in T-SQL. The earlier parts were about NULL changing whether a row was kept or a number was counted. This one is about NULL in string building, where one NULL operand makes the whole result NULL.
Here is the setup, using the product table from the series. One product has no size recorded, so the column is NULL.
|
1 2 3 4 5 6 7 |
INSERT INTO dbo.product (product_id, sku, product_name, category_id, list_price) VALUES (100, 'BOLT-08', 'M8 Hex Bolt', 1, 0.12) , (101, 'NUT-08', 'M8 Hex Nut', 1, 0.05); /* pretend product 100 has a size and product 101 does not */ ALTER TABLE dbo.product ADD size_label varchar(20) NULL; UPDATE dbo.product SET size_label = 'M8' WHERE product_id = 100; |
Now the label. Product name, a dash, the SKU, then the size in parentheses.
|
1 2 3 4 5 6 7 |
SELECT product_id , product_name + ' - ' + sku + ' (' + size_label + ')' AS label FROM dbo.product; /* product_id label 100 M8 Hex Bolt - BOLT-08 (M8) 101 NULL */ |
Product 100 gets the label you designed. Product 101 gets NULL, not just a missing size in an otherwise fine string. The whole label is gone, name and SKU included, because size_label was NULL.
The cause is the rule that the + operator follows for NULL. If either operand of + is NULL, the result is NULL, and that NULL then flows through the rest of the expression. For product 101, ... + '(' + NULL + ')' is NULL, and once any part of the chain is NULL the entire concatenation is NULL. The same thing happens if sku or any other operand is NULL, not only the size. This behavior is separate from three-valued predicate logic. It is the NULL propagation rule for the + operator, and it is governed by CONCAT_NULL_YIELDS_NULL, which follows the ANSI NULL semantics.[1] Since SQL Server 2017 that setting is always ON[2], and SET CONCAT_NULL_YIELDS_NULL OFF is deprecated and no longer restores the old empty-string behavior on a supported version. New code should not depend on the OFF behavior.
The usual fix is CONCAT, which treats a NULL operand as an empty string.[3]
|
1 2 3 4 5 6 7 |
SELECT product_id , CONCAT(product_name, ' - ', sku, ' (', size_label, ')') AS label FROM dbo.product; /* product_id label 100 M8 Hex Bolt - BOLT-08 (M8) 101 M8 Hex Nut - NUT-08 () */ |
Now product 101 keeps its name and SKU, and the empty parentheses show where the size would go. CONCAT also converts its arguments to string types for you, so it avoids the data-type precedence issues you get from +, where mixing a string with a number can do arithmetic or fail to convert and a date needs an explicit CONVERT to format the way you want. One thing to watch is that the parentheses here are separate literal arguments, so CONCAT returns empty parentheses when size_label is NULL rather than dropping the ( ) altogether. If you want the size section to disappear when there is no size, you have to build that in yourself.
When you want a NULL to become specific text rather than nothing, wrap that column in COALESCE. If you are still using +, handle every operand that can be NULL, because one unhandled NULL is enough to blank the whole result.
|
1 2 3 4 5 |
SELECT product_id , CONCAT(product_name, ' - ', sku, ' (', COALESCE(size_label, 'no size'), ')') AS label FROM dbo.product; /* 101 M8 Hex Nut - NUT-08 (no size) */ |
There is also CONCAT_WS, available from SQL Server 2017, which takes a separator as its first argument and places it between the value arguments. It skips NULL value arguments when it places separators, so a NULL does not leave a doubled separator, which is handy for comma-joined lists. It needs the separator plus at least two values, and note that it skips NULL but not an empty string.
The reason this one reaches production is that the + label works for every row where all the parts are present, and tests built from non-NULL sample data will not show the behavior. The result becomes NULL only when at least one concatenated operand is NULL, and when that happens there is no error and no truncation. The label field can come back as NULL, and in a report or an export that field may appear empty rather than flag anything wrong.
Next up is Part 7, ISNULL and COALESCE are not interchangeable, where the two functions you reach for to handle a NULL differ on return type, on string length, and on how many times they evaluate their input.
If a NULL has ever blanked out a label or an export line for you, I would like to hear about it. You can find me on Bluesky and LinkedIn.
References
- SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) - Microsoft Learn. Documents the setting that makes + return NULL when an operand is NULL, and its deprecation. ↩
- NULL Complexities, Part 2 - SQLPerformance, Itzik Ben-Gan. Shows a + concatenation returning NULL when any operand is NULL, and recommends CONCAT_WS. ↩
- CONCAT (Transact-SQL) - Microsoft Learn. Documents CONCAT, which converts arguments to strings and treats NULL as an empty string. ↩