SQL Concatenate column values and store in an extra column

I am using SQL Server 2019 (v15.0.2080.9) and I’ve got a simple task but somehow I am not able to solve it…

I have a little database with one table containing a first name and a last name column

CREATE TABLE [dbo].[person]
(   
    [first_name] [nchar](200) NULL,
    [last_name] [nchar](200) NULL,
    [display_name] [nchar](400) NULL
) ON [PRIMARY]
GO

and I want to store the combination of first name with an extra whitespace in between in the third column (yes I really have to do that…).

So I thought I might use the CONCAT function

UPDATE [dbo].[person]
SET display_name = CONCAT(first_name, ' ', last_name)

But my display_name column is only showing me the first name… so what’s wrong with my SQL?

Kind regards Sven

Answer

Your method should work and does work. The issue, though is that the data types are nchar() instead of nvarchar(). That means they are padded with spaces and the last name is starting at position 201 in the string.

Just fix the data type.

In addition, I would suggest that you use a computed column:

alter table person add display_name as (concat(first_name, ' ', last_name));

This ensures that the display_name is always up-to-date — even when the names change.

Here is a db<>fiddle.

As a note: char() and nchar() are almost never appropriate. The one exception is when you have fixed length strings, such as state or country abbreviations or account codes. In almost all cases, you want varchar() or nvarchar().