COALESCEand Non Null Values - scott

I use the below concatenation of the first, last name, middle initial and
suffix.using the COALESCE function. It works fine and renders the middle
initial and and a period if it the userMI field contains a letter or leaves
a space if the middle initial is null. The problem I've noticed is when the
middle initial field called userMI is not null, but is empty, my COALESCE
statement will insert the period and a space.

I posted an example below. Tim Brock's userMI field is blank. It doesn't
contain an empty space and has no length, but it isn't null. Therefore,
COALESCE thinks the empty field needs the period.

Is there a better way to evaluate the userMI field and only render the
userMI value with the period only if there is a "letter" in the field?

CODE ************
SELECT userLast, userFirst, userMI,

u.userFirst + COALESCE (' ' + u.userMI + '. ', ' ') + u.userLast + COALESCE
(' ' + u.userSuffix + '', '') AS FullName

FROM users u



EXAMPLE ***************

userLast userFirst     userMI      FullName
----------------------------------------------------------------
Smith      John              Q              John Q. Smith
Jones      Mike              NULL      Mike Jones
Brock      Tim                 {blank}   Tim . Brock

COALESCEand Non Null Values - Tom Cooper

You can use the NullIf function to treat blanks and the empty string as
NULLs, like
SELECT userLast, userFirst, userMI,

u.userFirst + COALESCE (' ' + NullIF(u.userMI, '') + '. ', ' ') + u.userLast
+ COALESCE
(' ' + u.userSuffix + '', '') AS FullName

FROM users u

Tom