How do I check for C.NAME_SUFFIX same as I'm checking for C.MIDDLE_NAME in the following query?
CASE WHEN C.MIDDLE_NAME = ''
OR C.MIDDLE_NAME = ' '
OR C.MIDDLE_NAME IS NULL
C.FIRST_NAME || C.MIDDLE_NAME || C.LAST_NAME
C.FIRST_NAME || ' ' || C.MIDDLE_NAME || ' ' || C.LAST_NAME
A different solution, based on the
case condition, Oracle's treatment of empty strings as null, and Oracle's and SQL standard's three-valued logic, looks like this:
with c ( first_name, middle_name, last_name , name_suffix) as ( select 'John' , 'D' , 'Smith' , ' ' from dual union all select 'Mary' , 'Ann' , 'Loury' , null from dual union all select 'Chris' , null , 'Robson' , 'III' from dual union all select 'Greg' , 'Z.' , 'Olson' , 'Jr.' from dual union all select 'Helen' , ' ' , 'Webb-Hunt', ' ' from dual ) select first_name || ' ' || case when middle_name != ' ' then (middle_name || ' ') end || last_name || case when name_suffix != ' ' then (' ' || name_suffix) end as full_name from c;
FULL_NAME ----------------------- John D Smith Mary Ann Loury Chris Robson III Greg Z. Olson Jr. Helen Webb-Hunt