How to do not null check on LEFT function on the select query

I have a query that returns some demographics like firstName, lastName, MiddleName and i need to use LEFT function on each to filter the First Letter of each column like LEFt(firstName, 1).This is working fine when each column is not a null value. when it is null value

 select otherColumns, LEFT(sub.LastName, 1) + ',' + LEFT(sub.FirstName, 1) + ' ' + LEFT(sub.MiddleName, 1) as patientInitials from <table> <inner joins> <some where conditions>;

But when one of demographics like middleName is null and other firstName, lastName are not null , patientInitials are evaulating to NULL, not sure why?

I resolved my issue by adding COALESCE

 LEFT(sub.LastName, 1) + ',' + LEFT(sub.FirstName, 1) + ' ' + COALESCE((LEFT(sub.MiddleName, 1)),'') as patientInitials

But is there any other good way to check for notNull on the LEFT function ??

Help Appreciated!

Answer

But is there any other good way to check for notNull on the LEFT function ??

CONCAT function ignores NULLs:

SELECT CONCAT(LEFT(sub.LastName, 1), ',' , 
              LEFT(sub.FirstName, 1),
              ' ' + LEFT(sub.MiddleName, 1)) patientInitials
FROM tab;

' ' + LEFT(sub.MiddleName, 1)) using ‘ ‘ will remove leading space in case if Middle Name is NULL.

Advertisement

Answer