I have a database containing a nmae broken down into constituent parts. I would like to string them together for a view as "Lastname, Firstname I." Unfortuantely, middle initial is not required and the NULL results in NOTHING appearing in this column of my view (the name looks fine as long as the field is not NULL).

How can I convert a NULL database field to a SPACE so that this error does not occur?

Hi Ron,

You can take a look at the coalesce function. This functions returns the first none null element you pass to it. Since adding a space to null still results in null, you could do it like this:

SELECT FirstName + coalesce(' ' + Initials + ' ', ' ') + LastName AS FullName FROM Users

This will test Initials. When it's not null, a space followed by the initials followed by another space is returned. If the initials are null, just a single space (to separate the first name from the last name) is returned.
You can change the stuff you pass to coalesce if other fields can be null as well. In that case, you don't want the initials to be responsible for adding the spaces.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Thirty-Three by Smashing Pumpkins (Track 3 from the album: Mellon Collie and the Infinite Sadness - Twilight to Starlight) What's This?

I figured there'd be something that would do this, but COALESCE wasn't the first search term that leapt to mind. Tried and worked as advertised. Thanks, Imar!

Yeah, odd name, isn't it. You'd expect something like IsNull. But maybe that was too obvious ;)

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Dreamland by The B-52's (Track 5 from the album: Good Stuff) What's This?

Hey,
I don't even know how to pronounce it!

Thnx for the tip. (You're everywhere, Imar!)
Pettrer

Coding is indeed a nine-to-five job; nine pm to five am.

;)

You're welcome....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.