Menu
I am creating a computed column across fields of which some are potentially null.
The problem is that if any of those fields is null, the entire computed column will be null. I understand from the Microsoft documentation that this is expected and can be turned off via the setting SET CONCAT_NULL_YIELDS_NULL. However, there I don't want to change this default behavior because I don't know its implications on other parts of SQL Server.
Is there a way for me to just check if a column is null and only append its contents within the computed column formula if its not null?
733
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL . When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string).
One way to concatenate multiple strings or columns is to use the "+" operator. For the rows where MiddleName is NULL, the concatenation result will be NULL.
Using the String.concat() method is a good choice when we want to concatenate String objects. The empty String returned by the getNonNullString() method gets concatenated to the result, thus ignoring the null objects.
Concatenating Data When There Are NULL ValuesTo resolve the NULL values in string concatenation, we can use the ISNULL() function. In the below query, the ISNULL() function checks an individual column and if it is NULL, it replaces it with a space.
You can use ISNULL(....)
SET @Concatenated = ISNULL(@Column1, '') + ISNULL(@Column2, '') If the value of the column/expression is indeed NULL, then the second value specified (here: empty string) will be used instead.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
