[RESOLVED] Concating Null Values

[Mark Gambo]

I have the following SQL Statement:

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?

[Mark Gambo]

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

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

[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.

[Mark Gambo]

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!

[Mark Gambo]

Steve I tried to rep you but:

[szlamany]

Thanks for trying!

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