ISNULL equivalent for empty fields

Question

Is there something like ISNULL() OR COALESCE() but not that checks for null value but for an empty value.

for example:

  SELECT cu.last_name, cu.first_name, cu.email, hu.email FROM 
 (SELECT DISTINCT c.first_name, c.last_name, c.email, c.household_id, h.head_of_household_id
  FROM rd_customers c
  JOIN rd_households h ON c.household_id = h.household_id
  JOIN ad_registrations r ON r.customer_id = c.customer_id
  JOIN ad_meeting_times a ON  r.session_id = a.session_id and a.meeting_time_id = 203731) cu
  LEFT JOIN rd_customers hu ON hu.customer_id = cu.head_of_household_id

instead of returning four columns I'd like to get three and the third one suppose to have values either of cu.email or hu.email if the first one is EMPTY! ISNULL and COALESCE don't work here i dunno why

Answer 1

You can always use case:

select  case when cu.email is null or cu.email = '' then hu.email 
             else cu.email end as ColumnName
from    YourTable

Answer 2

This will catch null or empty cu.email and use hu.email instead:

SELECT cu.last_name, 
       cu.first_name, 
       CASE WHEN ISNULL(cu.email, '') = '' THEN
            hu.email
       ELSE
            cu.email
       END AS email
FROM 
(
  SELECT DISTINCT c.first_name, 
                  c.last_name, 
                  c.email, 
                  c.household_id, 
                  h.head_of_household_id
  FROM rd_customers c
  JOIN rd_households h 
    ON c.household_id = h.household_id
  JOIN ad_registrations r 
    ON r.customer_id = c.customer_id
  JOIN ad_meeting_times a 
    ON  r.session_id = a.session_id 
      AND a.meeting_time_id = 203731
) cu
LEFT JOIN rd_customers hu 
  ON hu.customer_id = cu.head_of_household_id

Answer 3

look at NULLIF (Transact-SQL)

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

try this:

COALESCE(NULLIF(cu.email,''),NULLIF(hu.email,''))

will return NULL if cu.email and hu.email are any combination of empty string or NULL