Deriving a computed column based on several nullable columns

I'm a bit of an SQL novice.

I've been trying to create an auto generated column called TAG from 5 other columns, some of which can retain NULL marks.

The syntax used to create the auto generated column is:

[Tag]  AS ([Tag Type]+[Parent Tag Type]+[Tag Area No]+[Tag seq No]+[Tag Suffix])

This autogenerates OK if none of the five columns retains a NULL, but if I try to create a row with any one of the columns containing a NULL, the auto generate does not work, and leaves the TAG column blank, even though it creates the row OK with the all the individual column data.

Is there a way I can get the auto generate to work with NULLs?

You could use the CONCAT function in SQL Server 2012 or later, which automatically ignores NULL, and implicitly converts the inputs to string types if necessary.

Example:

CREATE TABLE #Demo
(
    [Tag Type] char(1) NOT NULL,
    [Parent Tag Type] char(1) NULL,
    [Tag Area No] integer NOT NULL,
    [Tag seq No] integer NOT NULL,
    [Tag Suffix] char(1) NULL,

    [Tag] AS 
        CONCAT([Tag Type], [Parent Tag Type], [Tag Area No], [Tag seq No], [Tag Suffix])
);

INSERT #Demo
    ([Tag Type], [Parent Tag Type], [Tag Area No], [Tag seq No], [Tag Suffix])
VALUES
    ('T', NULL, 1234, 1, NULL),
    ('T', 'P', 5678, 1, 'S');

SELECT * FROM #Demo AS D;

Output:

Tag Type | Parent Tag Type | Tag Area No | Tag seq No | Tag Suffix | Tag
:------- | :-------------- | ----------: | ---------: | :--------- | :-------
T | null | 1234 | 1 | null | T12341
T | P | 5678 | 1 | S | TP56781S

... and just in case the example was simplified and a separator is to go between the elements, check out CONCAT_WS() introduced in SQL Server 2017.

StackExchange Database Administrators Q#209709, answer score: 8