CODEWITHSUNDEEP
sqlsql-server
emo
emo

Reputation: 137

How do I update a VARCHAR column so that it contains the contents of two other VARCHAR columns, separated by a space?

I am new to SQL, which is perhaps why I have not been able to locate a solution to the following problem.

I have this database table where all columns are of type VARCHAR(50):

f

And I want to update the values in FullName so that they are a combination of FirstName and LastName, separated by a space.

This is what I would like to end up with:

f

I do not want to update each row manually.

Upvotes: 0

Views: 5819

Answers (1)

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84735

I do not want to update each row manually.

I will show you two solutions:

  1. UPDATE the column with a single statement. It's not really such a hassle:

    UPDATE Names
SET FullName = FirstName + ' ' + LastName;
-- or:     … = CONCAT(FirstName, ' ', LastName);

    You said that all your columns are defined as VARCHAR(50), so take note that FirstName + ' ' + LastName could reach a maximum length of 50 + 1 + 50 = 101 characters. Therefore there is the possibility that FullName gets truncated: That column will only store the first 50 characters (or less); if there are more, they get thrown away. So you might want to re-define FullName as VARCHAR(101).

  2. Alternatively, turn FullName into a computed column:

    ALTER TABLE Names
DROP COLUMN FullName;

ALTER TABLE Names
ADD FullName AS (FirstName + ' ' + LastName);

    This solution has three distinct advantages:

    1. FullName can never contradict FirstName and LastName, because it is automatically derived (at the time of querying it) from them via the specified expression.
    2. There is no danger of string truncation, as with the above solution. Note that you do not need to specify a type for FullName; SQL Server figures it out automatically, based on the expression.
    3. FullName will not take up any storage space (unless perhaps when it is included in an index).

Upvotes: 1

Related Questions