[RESOLVED] Concating Null Values

I have the following SQL Statement:

VB Code:

SELECT VEN.VENNUM, (VEN.TITLE + ' ' + VEN.FNAM + ' ' + VEN.LNAM) AS PERSON ...

When any of the fields inside of the PERSON Field are Null it returns a Null. How can I prevent this?

As I posted this it came to me Use the Coalesce:

VB Code:

SELECT VEN.VENNUM, LTrim(Coalesce(VEN.TITLE,'') + ' ' + Coalesce(VEN.FNAM,'') + ' ' + Coalesce(VEN.LNAM,'')) AS PERSON ...

You can avoid the LTRIM's by replacing

LTrim(Coalesce(VEN.TITLE,'') + ' '

With

Coalesce(VEN.TITLE+' ','') + ...

Put the space concatenation in with the potentially NULL column in the COALESCE - so if null, no title and no space.

We do that often.

Quote:

---

Originally Posted by szlamany

You can avoid the LTRIM's by replacing

LTrim(Coalesce(VEN.TITLE,'') + ' '

With

Coalesce(VEN.TITLE+' ','') + ...

Put the space concatenation in with the potentially NULL column in the COALESCE - so if null, no title and no space.

We do that often.

---

you are the man! :cool:

Steve I tried to rep you but:

Thanks for trying!

Us people in the DB section never get enough travel in the forum to avoid that restriction :)