October 25, 2011 at 9:55 am
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)
October 25, 2011 at 10:01 am
Try LEFT(middlename + '.', 1) instead.
That'll work depending on your concat_null_yields_null setting.
October 25, 2011 at 11:35 am
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