Select not returning Null Record

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

SQL Server 2008 Forums

Transact-SQL (2008)

Select not returning Null Record

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2011-12-14 : 11:33:39

I am running a simple select statement.

SELECT distinct a.Id, a.Registration_No, Convert(nvarchar(15), a.ticket_date, 106) as ticket_date_text, a.Forename + ' ' + a.Surname as Name FROM tblTickets a

The problem I am having is that a.Forename and a.Surname can be NULL sometimes and if they are that record is not returned.

Anyone know what I am missing?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 11:35:46

[code]
SELECT distinct a.Id, a.Registration_No, Convert(nvarchar(15), a.ticket_date, 106) as ticket_date_text, COALESCE(a.Forename + ' ','') +COALESCE( a.Surname,'') as Name FROM tblTickets a
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-15 : 03:14:35

Hi,

When we concatenate any variable/ column with some other variable/ column, if any one of the value is NULL then the result will be a NULL.

For Ex:

DECLARE @V1 VARCHAR(10) = NULL
DECLARE @V2 VARCHAR(5) = 'HAI'

SELECT @V1 + @V2
RESULT : NULL

SELECT COALESCE(@V1, '') + COALESCE(@V2, '')
RESULT : HAI

So use ISNULL OR COALESCE Statement while concatenating values.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources