Full name column from last name and first name

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin?

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.