7

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?

8

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.

6

Yes, you'll need to use ISNULL to do it though:

ISNULL([Tag Type], '') +
ISNULL([Parent Tag Type], '') +
ISNULL([Tag Area No], '') +
ISNULL([Tag seq No], '') +
ISNULL([Tag Suffix], '')

But fair warning, if any row has NULL across the whole thing, you'll end up with an empty string. You may want to wrap the entire thing in ISNULL with a canary value to avoid that if necessary.

Depending on data types, you may also need to CONVERT your columns to a string type inside ISNULL to avoid errors.

  • Fully agree. Pro-tip: doing it this way (or the OP way) has a problem: the tag for Area=Null, Seq=1 is the same as Area=1, Seq=Null. This will never happen to you of course -- until it does. Anyway, it is easy enough to fix: just add in a separator (ISNULL(…, '') + '~' + ISNULL...), OR a value for NULL that will never occur with real data. like ISNULL(…, '~') + ISNULL... – Stu Jun 15 '18 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.