Concatenating Fields With NULL Values

Hey everyone,

This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.

Here is my current SELECT statement:

SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.

The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.

Can someone tell me what I am missing? Is there a function that I can use for this?

Thanks,

Use ISNULL ( check_expression , replacement_value ) function

SELECT ISNULL(m.FName,'') + SPACE(1) + ISNULL(m.MName,'') + SPACE(1) + ISNULL(m.LName,'') + ', ' + ISNULL(m.Credentials,'') AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

|||

Hmm, I had never seen the SPACE function :) Only thing to suggest to your code is that you probably want to include the SPACE function calls in the ISNULL to get rid of extra useless spaces:

ISNULL(m.FName + SPACE(1),'')

|||

Thanks to both of you. I have even used ISNULL in the past. I feel pretty stupid now.

The reason I am using SPACE(1) is because I have seen SQL treat ' ' as an empty string. I think it has to do with sp_dbcmptlevel.

|||

Here's what I actually ended up doing:

SELECT REPLACE(RTRIM(m.FName + SPACE(1) + ISNULL(m.MName, '')) + SPACE(1) + m.LName + ', ' + ISNULL(m.Credentials, '*'), ', *', '') AS Name
FROM tblMembers m
WHERE m.DRGInclude = 1

The RTRIM gets rid of the extra space when MName is NULL, and to get rid of the ', ' when Credentials is null, I used the REPLACE function.

|||

Check out the property 'CONCAT_NULL_YIELDS_NULL'

you can turn it OFF by the following command

SET CONCAT_NULL_YIELDS_NULL OFF
GO

Thanks,
Loonysan