Concatenated SQL Column returning null value, is there a work around?

Question

I have a sql statement that concatenates two columns, ItemNumber and Name, into one variable. I use this variable as the DataTextField for a listbox. Everything seemed to work great. I did find a problem though.. Some of the rows have a null value in the Name field, and when it returns the variable, the variable is null.

So I am asking if there is any way to check for that null value and instead put just the item number in its place?

Here is my SQL statement

SELECT ProductID, Name, CustItemNum, CustItemNum + ' - ' + Name AS itemNumName
FROM Item it inner join JITVendors jit on it.Catalog_code = jit.Catalog_code 
WHERE jit.VendorName = 'Vendor' 
ORDER BY Name

If there is any other code that could be helpful let me know and I will be able to provide it. Thank you

Answer 1

You can use coalesce to replace a null name with an empty string:

CustItemNum + coalesce(' - ' + Name,'') AS itemNumName