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