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



Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.