5

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?

16

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

0

if i'm getting your question correct :

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.