COALESCE - SQL Server Forums

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

COALESCE

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2010-02-12 : 04:38:13

I have the following expressionin a SQL query

COALESCE (CA_Title, N'') + N' ' + COALESCE (CA_Firstnames, N'') + N' ' + COALESCE (CA_Surname, N'')

It works unless there is a null in one of the fields when it return a blank string. I thought COALESCE was supposed to cope with this ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-12 : 04:45:58

It works.
Can you show the entire statement?
Do you say it returns NULL?

No, you're never too old to Yak'n'Roll if you're too young to die.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2010-02-12 : 04:48:04

Its in a dataset in a windows app. It works if one of the field is a zero length string but not if one contains a null. I am databinding a lable to it using a tableadpater.

SELECT CA_Id, CA_Surname, CA_Firstnames, CA_Title, CA_LLPG, CA_Add1, CA_Add2, CA_Add3, CA_Add4, CA_Add5, CA_Pcode, CA_DateReported, CA_OffenceDate, CA_Team, CA_Officer,
CA_DetailsofOffence, CA_Action, CA_SFPRef, CA_CompletedDate, CA_FlareRef, CA_RemindMe, CA_Completed, CA_Section, CA_DeadLine, COALESCE (CA_Title, N'')
+ N' ' + COALESCE (CA_Firstnames, N'') + N' ' + COALESCE (CA_Surname, N'') AS Fullname, CA_Legislation, CA_TimeLine, CA_DateToLegal, CA_LegalId, CA_LEGDecision,
CA_DecisionDate, CA_LEGFilename, CA_OtherDefendants, CA_ReporttoDirector, CA_DateRTDDecided, CA_RTDNotes, CA_DirectorsDecision, CA_DirectorsDecisionNotes,
CA_EvidentialTest, CA_PublicInterest, CA_DecisionVerified, CA_DateVerified, CA_LegalNotes, CA_email, CA_StatsDecision
FROM tblPP_Cases

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 04:49:13

Works for me:


DECLARE @Temp TABLE
(
	CA_ID		int IDENTITY(1,1),
	CA_Title	nvarchar(20),
	CA_Firstnames	nvarchar(20),
	CA_Surname	nvarchar(20)
)

INSERT INTO @temp
SELECT	'Mr', 'Kristen', 'Netsirk' UNION ALL
SELECT	NULL, 'Kristen', 'Netsirk' UNION ALL
SELECT	NULL, NULL, 'Netsirk' UNION ALL
SELECT	NULL, NULL, NULL UNION ALL
SELECT	'Mr', NULL, 'Netsirk' UNION ALL
SELECT	'Mr', NULL, NULL UNION ALL
SELECT	'Mr', 'Kristen', NULL

SELECT	COALESCE (CA_Title, N'') + N' ' + COALESCE (CA_Firstnames, N'') + N' ' + COALESCE (CA_Surname, N'')
FROM @Temp
ORDER BY CA_ID

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 04:50:03

P.S. Results:


Mr Kristen Netsirk
 Kristen Netsirk
  Netsirk
  
Mr  Netsirk
Mr  
Mr Kristen

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 04:51:33

P.P.S. although what you probably want is:


RTrim(COALESCE(CA_Title + N' ', N'') + COALESCE(CA_Firstnames + N' ', N'') + COALESCE(CA_Surname, N''))

Pinto
Aged Yak Warrior

590 Posts

Posted - 2010-02-12 : 05:06:27

It returns the correct value in the query, but doesn't display it in the databound label on my windows form if there is a null in one of the fields. If I replace the null with a zero length string it does display - I suppose I'll have to find another forum for windows forms. At least I know the Coalesce is correct.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-12 : 05:18:48

hm...
So if Coalesce is working then how can a null value reach the windows form?
I am a bit confused...

No, you're never too old to Yak'n'Roll if you're too young to die.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2010-02-12 : 05:33:52

A null value doesn't. A blank string is returned. I've sorted it now anyway by editing the properties of the fields in the dataset so that a new record defaults those fields to a zero length string if nothing is entered

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 05:44:46

"editing the properties of the fields in the dataset so that a new record defaults those fields to a zero length string if nothing is entered"

Rather defeats the point of having a column that allows NULLs - you can no longer tell the difference between a record that is entered with no information, and one where the data is entered (e.g. by a user) but the fields are left blank.

You could perhaps use this (if it would be acceptable for your application)


COALESCE(
    NullIf(
        RTrim(
            COALESCE(CA_Title + N' ', N'') + COALESCE(CA_Firstnames + N' ', N'') + COALESCE(CA_Surname, N'')
        ), ''
    ), '(none)'
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources