CODEWITHSUNDEEP
sql
Cheknov
Cheknov

Reputation: 2082

SQL How to concat two columns adding logical conditions

Let's say I have a table with the following columns:

+------+------+------+
| Col1 | Col2 | Col3 |
+------+------+------+
| A    | A    | .    |
| B    | .    | B    |
| C    | .    | .    |
+------+------+------+

If I concat Col2 with Col3 I'm getting this result:

+------+--------+
| Col1 | Col2+3 |
+------+--------+
| A    |   A.   |
| B    |   .B   |
| C    |   ..   |
+------+--------+

But the desired result would be the following:

+------+--------+
| Col1 | Col2+3 |
+------+--------+
| A    |    A   |
| B    |    B   |
| C    |    .   |
+------+--------+

How could I do that?

{UPDATE} What I've tried so far:

SELECT Col1, COALESCE(NULLIF(Col2,'.'),NULLIF(Col3,'.')) FROM db.table;

It works partially: If Col2 = '.' and Col3 = '.' the result is [NULL] and the desired output would be '.', not '..' and not [NULL]

Upvotes: 0

Views: 83

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You are already using COALESCE(). Just add another argument:

SELECT Col1,
       COALESCE(NULLIF(Col2,' .'), NULLIF(Col3, '.'), '.')
FROM db.table;

If the first two arguments are NULL, then it will return '.'.

Upvotes: 0

DBASurvivor
DBASurvivor

Reputation: 1

you can just add an ISNULL to your solution

SELECT Col1
    , ISNULL( COALESCE(NULLIF(Col2,'.'),NULLIF(Col3,'.')) , '.')
FROM table

Upvotes: 0

George Joseph
George Joseph

Reputation: 5922

Here is way to do this concatenate the two columns and replace dot with empty string, after that if its blank/empty_string(happens when both columns have dots),then replace it with a single dot

select col1
       ,case when replace(concat(col2,col3),'.','')='' then
                  '.' 
             else replace(concat(col2,col3),'.','') 
         end as col2_3
  from table

Upvotes: 0

Anant Dabhi
Anant Dabhi

Reputation: 11104

There are couple of option avaiable to check null and select value which is not null for E.g :

 Case Condition 

CASE  
WHEN (Col2 IS NOT NULL) THEN Col2  
WHEN (Col3 IS NOT NULL) THEN Col3  
ELSE '.'  
END

another option

COALESCE(Col2, Col3) AS [Col2+3]

** you may need to change syntax as per your usage above syntax for just an example.

Upvotes: 2

Related Questions