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

"John Morgan" <jfm@XXwoodlander.co.uk> wrote in message
news:i7sq801rns7bfjli57ado8atitkdll96um@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

John Morgan (jfm@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, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks Simon and Erland for your help, its much appreciated,
Best wishes, John Morgan