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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.