how to handle null values in COALESCE statement??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to handle null values in COALESCE statement???

Author

Topic

sweet_777
Starting Member

16 Posts

Posted - 2010-10-22 : 02:16:01

Hi all,

in my table i have a column:empname
values are(emp1,emp2,emp3,null,null)
while i am concatenating these values i am using this query:
declare @a varchar(1024)
select @a=COALESCE(@a+ ',', '') + empname
FROM emptable
output :,emp1,emp2,emp3,,,if null values are there in column means it was returning space comma
i want to get ,emp1,emp2,emp3how to do that

Regards
Sweety

Thanks & Regards
Sweet_77

Kristen
Test

22859 Posts

Posted - 2010-10-22 : 02:30:05

select @a= CASE WHEN empname IS NULL THEN '' ELSE COALESCE(@a+ ',', '') + empname END

or add

SELECT ...
FROM emptable
WHERE empname IS NOT NULL

Kristen
Test

22859 Posts

Posted - 2010-10-22 : 02:30:52

P.S. You probably should put an ORDER BY on your SELECT statement so that the results are repeatable (use the PK column if nothing else)

sweet_777
Starting Member

16 Posts

Posted - 2010-10-22 : 02:38:58

Hi Kristen,

How are you ?

Thank you very much for your reply .

Regards
Sweety

Thanks & Regards
Sweet_77

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources