Concatenate fields with no values

[Pubcit]

I am running CF 5 with SQL Server 2000. I am looking to concatenate a person
name, but not all names have a suffix or middle name.

Sample SQL:
SELECT ID, Last_name + N' ' + Suffix + N', ' + First_Name + N' ' +
Middle_Name AS Full_Name
FROM tTable
ORDER BY ID

The problem is when someone does not have a suffix. The value of Full_Name
will then look like "Doe , John M." with an extra space after the last name.
Can I put in an IF statement in my SQL? This problem must be handled in the
SQL. Normally I would take care of this with CF code during the query output.

[mxstu]

Try using CASE ....

SELECT Last_name + CASE Suffix WHEN '' THEN N'' ELSE N' '+ Suffix END .....
FROM tTable
ORDER BY ID

Or if the Suffix values are NULL (and not an empty string), you could also use
the COALESCE() function.