How Coalesce works in sql server?

Question

Create table test(Names varchar(100) primary key )

insert into test values('Hugeman')
insert into test values('Jack')
insert into test values('William')
insert into test values('Kevin')
insert into test values('Peter')

Query 1:

declare @sql varchar(100)
select   @sql = coalesce(@sql+'+','')+Names from test order by names-- where object_id =object_id('temp')  
print @sql

This will result as Hugeman+Jack+Kevin+Peter+William

Query 2

declare @sql varchar(100)
select   @sql = coalesce(Names+'+','')  from test order by names-- where object_id =object_id('temp')  
print @sql

This will results William+

As per the documentation of coalesce, will return the first not null value. So it has to result Hugeman+. But it returns the entire rows.

Why query2 haven't done the same ?

Answer 1

This is not stricly connected to COALESCE.

Try these SELECTs:

DECLARE @sql1 AS VARCHAR(1000)
SELECT @sql1 = ISNULL(@sql1, '') + Names FROM test ORDER BY Names
PRINT @sql1

DECLARE @sql2 AS VARCHAR(1000)
SELECT @sql2 = Names FROM test ORDER BY Names
PRINT @sql2

So what happened? For EACH record selected:

• in query 1 you keep adding values to @sql
• in query 2 you reset @sql as the last name extracted

I don't understand exactly what you want to obtain from your SELECT, but a better example of COALESCE could be:

CREATE TABLE TEST2(Name VARCHAR(100) PRIMARY KEY, Telephone VARCHAR(10), Mobile VARCHAR(10))

INSERT INTO TEST2 VALUES('Hugeman', 1, 2)
INSERT INTO TEST2 VALUES('Jack', NULL, 3)
INSERT INTO TEST2 VALUES('William', 4, NULL)
INSERT INTO TEST2 VALUES('Kevin', 5, 6)
INSERT INTO TEST2 VALUES('Peter', NULL, NULL)

SELECT Name,
    COALESCE(Telephone, Mobile) AS Tel
FROM TEST2

Answer 2

@sql is on the right hand side on the first to get appended

On the last @sql is all alone on the left side and has the value of the last row

Answer 3

Query-1: Appending the values of all rows to @sql Query-2: Re-setting values to @sql

The below is the rows with order by names

Hugeman Jack Kevin Peter William

Query-1 Appends all values to @sql. Query-2 Re-writing values to @sql, so the last row in the list is William so when you print the variable the last re-assigned value is printed.

Answer 4

The COALESCE() function returns the first non null value from the from the columns as parameters in COALESCE() function.

e.g.

SELECTCOALESCE(null,null,1,2)     ///return --1

SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name
FROM tblEmployee           //return 1st non null value from FirstName/MiddleName/LastName