Ask the Expert

Full name column from last name and first name

How do you create a default value of a column that is the concatenated value of two other columns? For example, I have a column for last_name and a column for first_name, and want to create a column for the full_name.

    Requires Free Membership to View

The stock answer is: with a view.

For starters, you do not want an additional actual physical column if you can help it. That would mean additional code when inserting or updating, because you have to specify the last_name and first_name values twice. It's also wasteful of space.

  view mynames
select first_name
     , last_name
     , coalesce(last_name||', ', '')
            || first_name       as full_name
  from names

Here the full name is constructed by concatenating the last name and first name columns with a comma and space as separators. (The full name is not actually a separate column, because the view doesn't exist beyond the definition of the view. Unless you materialize the view. But we're getting sidetracked.)

There is a special trick used here. The assumption is that people who have only one name—yes, it is perfectly legal to have just one name—will be registered in the table with this name as the first name and with NULL as the last name. Now, concatenating the comma-space onto NULL produces NULL, but then COALESCE turns that into a non-NULL empty string. Note that in this case, there is no comma and space. The result of the COALESCE is then concatenated with the first name, which will have been defined as NOT NULL. The final results would be:

first_name  last_name     full_name
----------  ---------     ---------
Joe         Btfsplk       Btfsplk, Joe
Cher        NULL          Cher

Neat, eh? Without the COALESCE trick, the concatenation of last name, comma, space, and first name would produce an overall result of NULL. You might then be tempted just to enter an empty string as Cher's last name, and that's just not right.

This was first published in November 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: