Tom Cooper
3/29/2007 4:29:00 PM
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
"scott" <sbailey@mileslumber.com> wrote in message
news:%23WAuG4hcHHA.4864@TK2MSFTNGP05.phx.gbl...
>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
>
>
>
>