Sql-server – if-then-else gives a boolean headache

sql serversql-server-2008-r2

I have three statements to combine but I cannot find the correct solution for SQL Server 2008 R2:

I need to concatenate three columns into one but in three different ways depending on the result in the last one. Column 3 can be a null-value, an alphanumeric letter or begin with '-' followed by a number. I tried the following:

if (select * from col3  where col3 is null)
 select col1 + ' ' + col2
else if (select * from col3 where col3 like '-%'
 select col1 + ' ' + col2 + col3
else if (select * from col3 where col3 not like '-%'
 select col1 + ' ' + col2 + ' ' + col3

Separately, they work like a charm. When trying to combine them like the above statement they throw me an error saying I have created a Boolean instead of the expected condition.

How do I formulate this to be correct?

Best Answer

The syntax you are looking for is the case statement. You can use IF .. EXISTS for controlling the flow in your code but for something like this the CASE WHEN statement is what you want.

SELECT CASE WHEN col3 IS NULL THEN col1 + ' ' + col2
    WHEN col3 LIKE '-%' THEN col1 + ' ' + col2 + col3
    WHEN col3 NOT LIKE '-%' THEN col1 + ' ' + col2 + ' ' + col3
    END AS ConcatCol
FROM TableName;

and simplified to:

SELECT col1 + ' ' + col2 
       + CASE WHEN col3 IS NULL THEN '' 
            WHEN col3 LIKE '-%' THEN col3
            ELSE ' ' + col3
         END AS ConcatCol
FROM TableName;

Assuming I understood what you are trying to do of course.

EDIT: Added alias for column

Related Question