Wednesday, January 17, 2007

How to decide whether any of the two strings is null in Oracle?

I ran into a problem in my project, I have to decide whether there is a string in a list of strings is null or not. The problem is we have two columns from the Oracle DB: first_name, last_name, both of them could be null, we need to format the fullname like "last_name, first_name", but we do not to have the ',' if either last_name or first_name is null. Here comes my solution:
select last_name || nvl2(length(last_name) * length(first_name), ',', null) || first_name from contact_info;

No comments: