Whilst studying for my MCITP Database Developer certification, I have come across a very useful function 1 that returns the value of the first non-NULL value from multiple choices:
SELECT LastName, FirstName, COALESCE(MobilePhone, HomePhone) AS Phone
If the MobilePhone field is blank, it will use the HomePhone value instead.
The difference between this function and ISNULL, is that COALESCE can accept multiple values:
COALESCE(field1, field2, …, fieldn)
- Please ensure you test any script taken from my website on a test/development machine, before running on a production server. ↩