string concat problem

[HRHK]

I have problem with string concatenation that I need some help with.
In the following SQL, I concatenate the contact first name and last name.
I fill it in dataset and bind it to datagrid. The problem is if there's no
contact name, it inserts a blank string (i.e. a space that I used in concatenation").
How can I avoid adding a blank string if there's no contact?
Thanks much.

SQL:
SELECT t_customer.cust_name, t_contact.first_nme+" "+t_contact.last_nme AS ContactName, t_customer.cust_id
FROM t_contact RIGHT JOIN t_customer ON t_contact.cust_id = t_customer.cust_id

cust_name ContactName cust_id
--------- ------------ ---------
Univ of Michigan Terese Bank 1
Merck 2
Pfizer 3
?iRow.IsNull("ContactName")
False
?iRow("ContactName")
" " {String}
String: " "

 

[Mastakilla]

If had this problem in access,
i used the iff(condition, truepart, falsepart) to resolve it.

it should look like:

iif(t_contact.first_nme+" "+t_contact.last_nme = " ", Null, t_contact.first_nme+" "+t_contact.last_nme) AS ContactName

if this exists in SQL

 

[Mastakilla]

I forgot to mention that this goes in your SELECT statement:

SELECT t_customer.cust_name, iif(t_contact.first_nme+" "+t_contact.last_nme = " ", Null, t_contact.first_nme+" "+t_contact.last_nme) AS ContactName, t_customer.cust_id 
FROM t_contact RIGHT JOIN t_customer ON t_contact.cust_id = t_customer.cust_id