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)||'?' 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) )
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.