CODEWITHSUNDEEP
sqlsql-servert-sql
Pradeep
Pradeep

Reputation: 1292

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!

Upvotes: 0

Views: 864

Answers (2)

Shine
Shine

Reputation: 26

The CONCAT_WS function also has a similar function：CONCAT_WS (Transact-SQL)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

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.

Upvotes: 1

Related Questions