Left Command Troubles with NULL Values

  • Hey there... I'm trying to concatenate a statement and I'm running into a tiny little problem (that may not be avoidable)...

    What I'm doing is concatenated the customer's first_name + middle_name + last_name. The problem is, I don't want the WHOLE middle name. I just want the users first initial. So I used the following LEFT(CUSTOMER.MIDDLE_NAME,1) +'. '+

    The problem is, the end users do not always put the middle name in the application. So if the CUSTOMER.MIDDLE_NAME IS NULL, the entire column returned is NULL. Is there a way to capture the middle_name first initial + '. ' if NOT NULL else

    first + last?

    Now here's what I did to make it work, but I'm wondering if the Guru's out there have a more proficient way to do this...?

    (SELECT

    CASE

    WHEN CUSTOMER.MIDDLE_NAME IS NOT NULL THEN CUSTOMER.FIRST_NAME + ' ' + LEFT(CUSTOMER.MIDDLE_NAME, 1) + '. ' + CUSTOMER.LAST_NAME

    ELSE CUSTOMER.FIRST_NAME + ' ' + CUSTOMER.LAST_NAME

    END) AS CUSTOMER_FULL_NAME,--CONCATENATED NAME (FIRST-MIDDLE INITIAL-LAST)

  • Try LEFT(middlename + '.', 1) instead.

    That'll work depending on your concat_null_yields_null setting.

  • Ninja's_RGR'us (10/25/2011)


    Try LEFT(middlename + '.', 1) instead.

    That'll work depending on your concat_null_yields_null setting.

    You really should test before posting. I think what you wanted was actually

    ISNULL(LEFT(middlename, 1) + '.', '')

    This should give you the results you want regardless of the ANSI_NULL settings and will also produce the correct results when middlename is not null, e.g., producing "A." instead of just "A".

    On a completely different note, as the number of potentially NULL fields grows, an approach that works well is using a subquery/CROSS APPLY that includes FOR XML PATH to do the concatenation. Since the FOR XML PATH automatically handles NULL expressions, it greatly simplifies the query when the number of NULLable fields increases.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply