Populating one name column from three

I have a table with four columns - full_name, first_name, middle_name, last_name. I want to use the values from the first_name, middle_name and last_name fields to populate the full_name column. How do I do that? I am using Oracle 7.3

The obvious solution is a straight-forward concatenation of the three column values --

update yourTable
   set full_name = 
         first_name
      || middle_name
      || last_name

Notice the absence of a WHERE clause; this means the update will be executed on every row of the table.

However, there are a couple of "gotchas" that you should be aware of.

First, if the columns are CHAR and not VARCHAR2 (or perhaps VARCHAR2 but improperly populated with leading or trailing blanks), then you need to TRIM the values --

update yourTable
   set full_name = 
         trim(first_name)||' '
      || trim(middle_name)||' '
      || trim(last_name)

Notice that now we need to insert an extra blank between the names.

But we're still not done. If the first or middle name is missing, the extra blank we're inserting will be superfluous. Now, standard SQL includes a function called NULLIF, but I'm not sure Oracle supports it -- however, Oracle has a function called DECODE which we can use here instead of NULLIF, even though DECODE is typically used in a slightly different manner (see What does the decode function do, and how is it structured? 18 April 2001).

update yourTable
   set full_name = 
         decode(trim(fname)||' ',' ',null,trim(fname)||' ')
      || decode(trim(mname)||' ',' ',null,trim(mname)||' ')
      || trim(lname)

Basically, the DECODE function here is used to compare the value trim(xxx)||'?'

    Requires Free Membership to View

    Login

against a blank, and if equal, return NULL, otherwise return that same value. If xxx itself has at least one non-blank in it (it can also have blanks in it, and they could be anywhere, at the front, in the middle, or trailing), then when it's trimmed, it will still have a non-zero length -- so trim(xxx)||'?' won't be equal to a blank, and therefore trim(xxx)||'?' is what is returned by DECODE. If you ask me, NULLIF is a lot simpler.

Finally, if either or both the first name or middle name are not blank but the last name is (e.g. Chuck D), then the above solution will still yield a trailing blank, so just TRIM the final result --

update yourTable
   set full_name = 
        trim(
         decode(trim(fname)||' ',' ',null,trim(fname)||' ')
      || decode(trim(mname)||' ',' ',null,trim(mname)||' ')
      || trim(lname)
            )

This was first published in March 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top