psftdeveloper psftdeveloper - 5 months ago 7
SQL Question

Check for name_suffix

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
THEN
C.FIRST_NAME || C.MIDDLE_NAME || C.LAST_NAME
ELSE
C.FIRST_NAME || ' ' || C.MIDDLE_NAME || ' ' || C.LAST_NAME
END

Answer

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;

Result:

FULL_NAME
-----------------------
John D Smith
Mary Ann Loury
Chris Robson III
Greg Z. Olson Jr.
Helen Webb-Hunt