CODEWITHSUNDEEP
sql-server-2008-r2
user2662100
user2662100

Reputation: 13

Not getting the desired results in a computed column when 1 or more of the columns used have NULLS

Did quite a bit of searching and found many examples of how to configure a computed column but could find nothing on this.

I have 2 columns - LastName and FirstName. In some instances one or the other of these columns is NULL.

My computed column syntax is 

((CONVERT([varchar](max),[LastName],(0))+',  ')+CONVERT([varchar](max),[FirstName],(0))

Which works great if both columns have data.
However, when one or the other is NULL the computed column is populated with NULL.

Upvotes: 1

Views: 36

Answers (1)

T I
T I

Reputation: 9943

Concatenating strings with null will result in null, see the remarks section here for more detail

You should handle the nulls as appropriate

CREATE TABLE People 
(
  Firstname VARCHAR(50), 
  Lastname VARCHAR(50) NOT NULL, 
  Fullname AS CAST(CASE WHEN Firstname IS NOT NULL 
                        THEN Lastname + ', ' + Firstname 
                        ELSE Lastname END AS VARCHAR(105))
)

demo

Arguably though, can it be a full name without both a first and last name, perhaps here null makes sense

Edit: if either Firstname or Lastname can be null then you'd want to do something like this instead

CASE WHEN Firstname IS NULL OR Lastname IS NULL THEN COALESCE(Firstname, Lastname)
     ELSE Lastname + ', ' + Firstname END

demo

Upvotes: 1

Related Questions