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?

score -1 · Answer 1 · answered May 21 '13 at 22:40

-1

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

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

answered May 21 '13 at 22:40

bobs

21,844
12
67
78

Surely this will never be `null` because you are putting in the `-` character – Neil Knight May 22 '13 at 07:47
Did you try it? When I tested, it worked as expected. Null concatenated to a non-null should give you null. You may need to check the ANSI NULL settings. – bobs May 22 '13 at 23:00
I did try it and I got the `-` – Neil Knight May 23 '13 at 07:21

Only display a character if the ISNULL has a valid entry

1 Answers1