Wednesday, March 12, 2008

Returning String Instead of NULL:

Consider a situation in which your database table contains NULL and you don't want to return NULL but some message. Like suppose you have a Person table and a Phone Table and a person does not have a phone number so we can easily return a message saying "No Phone Number Found" instead of returning a NULL.

SELECT P.Name, 'PhoneNumber'
= CASE WHEN Ph.PhoneNumber IS NULL THEN
'No Phone Number Exists'
ELSE
Ph.PhoneNumber
END
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID


You can also use the ISNULL method of the SQL SERVER 2000 to do the same operation. Check out the code below which returns the same result.

SELECT P.Name, Ph.PhoneNumber, ISNULL(Ph.CellNumber,'No Phone Number') AS CellNumber
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID

No comments: