Problem solve Get help with specific problems with your technologies, process and projects.

NULLs propagate through expressions

This is extremely simple but haven't found anyone that can answer. Let's say I concatenate two columns, LName +...

FName, to create column Membname. There is a LName but the FName exists only some of the time. For all of the values without a FName, the new column populates as NULL. How can I get the FName and LName, or LName only if the FName does not exist? I tried writing an IF statement to no avail. Must I create a stored procedure?

Use the COALESCE function. If LName will never be NULL, you'd need to use it only on the Fname column.

select LName 
     + coalesce( ', ' + Fname, '' ) 
          as Membname
  from yourtable

Note here how Fname is first concatenated to a string consisting of a comma followed by a space. Now since NULLs propagate through expressions, therefore if Fname is NULL, then the concatenation of ', ' and Fname will also be NULL, and thus, the COALESCE function will return a zero-length or empty string. Otherwise, the concatenation of ', ' and Fname will be concatenated to Lname.

The net effect is that Membname will be either Lname only, or Lname followed by a comma and space and then Fname. Sweet, eh?

This was last published in March 2005

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.

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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.