CODEWITHSUNDEEP
sqlsql-server
Dom Sinclair
Dom Sinclair

Reputation: 2528

Correct Use of Coalesce

I have three columns in a table A b and c. If a is not null then I want to return a but if it is null I want to return b + c.

I had thought that Select Coalesce(a, b + c) from table1 would do the trick but it doesn't seems to which suggests I have the syntax wrong. Each variant of this I try still seems to return null values for column a when it should be returning a concatenation of b & c because a is null. I'd welcome your assistance.

I think the syntax is correct, but It's the incorrect use of Coalesce.

Two tables of three columns:

Fred Flinstone Tyres    Fred    Flinstone
Norman Greembaum        Norman  Greenbaum
NULL                    Dave    Collins


NULL    2   3
4   5   6
8   6   8
NULL    2   7

Sorry the formatting of the second is not as good but it should suffice. Coalesce seems to work with the lower table when using int datatype but not with the top when usin nvarchar. Typically of course it's the top table i'm interested in, I would want Dave Collins returned for row 3.

Upvotes: 0

Views: 541

Answers (5)

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

Simply ISNULL(a, b+c) will do, in addition its SQL standard too for concatenation

ISNULL(a, CAST(b as varchar) + CAST(c as Varchar))

ANd for arithmetic 

ISNULL(a, ISNULL(b, 0) + ISNULL(c, 0))

Upvotes: 3

MarkD
MarkD

Reputation: 5316

If there is a change that either A or B might be NULL then;

ISNULL(a, ISNULL(b + c), 0)

Or

COALESCE(a, b + c, 0)

Upvotes: 0

YvesR
YvesR

Reputation: 6232

Use

SELECT COALESCE(a, b + c, b, c) FROM table1

or

SELECT COALESCE(a, IsNull(b + c,0)) FROM table1

Then you get a value for sure that is not null. In your case on of your values probably is null. Check also http://msdn.microsoft.com/en-us/library/ms190349.aspx

UPDATE:

DECLARE @table TABLE(a int, b int, c int);
INSERT INTO @table (a,b,c) VALUES (null, 1, 2);
INSERT INTO @table (a,b,c) VALUES (null, 2, 2);
INSERT INTO @table (a,b,c) VALUES (1, 3, 2);
INSERT INTO @table (a,b,c) VALUES (2, 4, 2);

SELECT COALESCE(a,b+c) FROM @table

results in:

3 4 1 2

as expected.

Upvotes: 0

user1915023
user1915023

Reputation: 5

SELECT COALESCE(A,B+C) FROM TABLE1 THERE IS TABLE TABLE1 AND COLUMN A,B,C IF THE VALUE OF (A=NULL) AND IF THE VALUE OF (B OR C) IS NULL THEN IT WILL RETURN NULL VALUE
table

a b c

1 2 3 1 NULL 3 2 NULL 3 2 NULL 4 NULL 2 4 NULL 2 NULL

after, select coalesce (a,b+c) from table1

1 1 2 2 6 NULL

Upvotes: -1

Dis Shishkov
Dis Shishkov

Reputation: 656

Check 'b' and 'c', I think one of them is null, because if you will sum 1 + null, it will be null

Upvotes: 0

Related Questions