Only display a character if the ISNULL has a valid entry

Question

In SQL Server, what I'm looking at doing is concatenating two fields together. So, for example, I have this layout:

Col1         Col2
Building A   Room 101
Building B   Room 2A
Building C   Room 301
Building D
Building E   Room 200

And I want to concatenate them like:

Building A-Room 101
Building B-Room 2A
Building C-Room 301

What is happening when it comes to Building D is I get:

Building D-

which is awful. I thought that I could do ISNULL([Col1] + '-', '') but this will always display the - character. Is there a way to only add the - value if [Col2] has a value without using a CASE statement? Or am I going about this in the wrong approach?

Answer 1

I think your test needs to evaluate Col2, not Col1. Try this

SELECT [Col1] + ISNULL('-' + [Col2], '')