Q

Full name column from last name and first name

Our SQL expert answers the question: "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."

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.

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.

create
  view mynames
as
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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close