By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Should SQL concatenation return a null value if one field is null?

P: n/a
In an SQL statement which concatenates several fields I get a null
value returned if any one of the fields are null.

Is this to be expected?

For example :

SELECT tblMember.memberAddress + ' ' + tblMember.memberTown + ' ' +
tblMember.memberCounty + ' ' + tblMember.memberPostCode + '<br> ' +
tblMember.memberCountry + '<br> ' + tblMember.memberInstitution AS
concatAddress FROM tblMember WHERE memberSurname='Cardy'

returns a null value if eg tblMember.memberInstitution is null.

Am I doing something wrong, if so I would be grateful for your help.
Otherwise it would be useful to know if there is some kind of work
around which can be used within the SQL statement (which is being
used in a stored procedure),

Best wishes, John Morgan
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"John Morgan" <jf*@XXwoodlander.co.uk> wrote in message
news:i7********************************@4ax.com...
In an SQL statement which concatenates several fields I get a null
value returned if any one of the fields are null.

Is this to be expected?

For example :

SELECT tblMember.memberAddress + ' ' + tblMember.memberTown + ' ' +
tblMember.memberCounty + ' ' + tblMember.memberPostCode + '<br> ' +
tblMember.memberCountry + '<br> ' + tblMember.memberInstitution AS
concatAddress FROM tblMember WHERE memberSurname='Cardy'

returns a null value if eg tblMember.memberInstitution is null.

Am I doing something wrong, if so I would be grateful for your help.
Otherwise it would be useful to know if there is some kind of work
around which can be used within the SQL statement (which is being
used in a stored procedure),

Best wishes, John Morgan


See SET CONCAT_NULL_YIELDS_NULL in Books Online. If this is ON, then you
will get a NULL, and it is recommended to have it on for the reasons
mentioned in the documentation. If there may be NULL values in your data
then you can use ISNULL() or COALESCE() to prevent them propagating:

select isnull(col1, '') + isnull(col2, '')...
from dbo.MyTable

Simon
Jul 20 '05 #2

P: n/a
John Morgan (jf*@XXwoodlander.co.uk) writes:
In an SQL statement which concatenates several fields I get a null
value returned if any one of the fields are null.

Is this to be expected?


Yes. This is a fundamental issue in SQL: if you have an expression that
includes a NULL value, and the value of the expression is dependent of
that value, then then value of the expression is NULL. NULL is an unknown
value, and no matter you concatenate to it, the value is still unknown.

Thus:

DECLARE @a int, @null int, @b char(2)

SELECT @a = 98, @null = NULL, @b = 'YH'

SELECT @a + @null -- NULL
SELECT power(@a, @null) -- NULL
SELECT @b + @null -- NULL

IF @a = @null PRINT 'is true' ELSE PRINT 'is not true' -- is not true
IF @a != @null PRINT 'is true' ELSE PRINT 'is not true' -- is not true
IF @a = 98 AND @null = 23 PRINT 'is true' ELSE 'is not true' -- is not true
IF @a = 98 OR @null = 23 PRINT 'is true' ELSE 'is not true' -- is true

The last is true, because here the condition @a = 98 alone determines the
value of the OR expression.

There are commands to change this, but I stronly recommend you to stay
away from them. They should only be used by legacy applications.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
Thanks Simon and Erland for your help, its much appreciated,
Best wishes, John Morgan

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.