Concatenation of patient name question

I have a report built that returns patient information. My code to concatenate the patient name fields (last name, first name, middle name and surname) work fine unless the middle name or surname fields are null, then it returns the concatenated patient name field as null. The code is posted below. Is there an easy method to determine if the field is null and then apply the correct logic to concatenate the name with the elements that are not null?

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_mname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_sname))

as SRM_PatientName

Hello,

Yes, you can. This should work.

ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_mname)), '')

+ ' '

+ isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

Hope this helps.

Jarret

|||

Use the following expression,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '')

as SRM_PatientName

|||

I created a lot of reports with concatenated columns at least 20 in different combinations and all I needed was the CONVERT function. But you need ISNULL or COALESCE so here are some examples and the link for SQL Server Concatenation documentation for more options. Hope this helps.

COALESCE(a,'') + COALESCE(b,'')

ISNULL(a, ”) + ISNULL(b, ”)

http://msdn2.microsoft.com/en-us/library/ms177561.aspx

|||

Thanks Jarrett and Manivannan. I have adapted your examples and they're working great. I appreciate your helpl