Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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

share|improve this answer
    
First solution worked. I encountered a problem naming the concatenated column. AS [name] have to be placed after CASE WHEN THEN END –  FishHead Feb 7 at 9:00

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 ...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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