473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
3 27721

"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
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
Thanks Simon and Erland for your help, its much appreciated,
Best wishes, John Morgan

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: yutlin | last post by:
Hello, Could anyone tell me if it is possible to bind a null value to a statement? I.E. $stmt->bind_param('isd',$var1,$var2,$var3); $var1 = (something indicating null); $var2 = (something...
3
by: John Morgan | last post by:
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 + '...
2
by: Joel | last post by:
Hi, I added a field to my company table (PBV_rstCompany.Fields("Installer")) the default value of the field is Null. I place this If statement and it doesn't work If...
3
by: Robb Gilmore | last post by:
Hello, We have a C#.NET app which is calling a Java webservice. We use the wsdl file exportted from the java webservice to create our web-reference in Visual Studio. We are able to create the...
2
by: Andy G | last post by:
How can I check this for null? dsPrsn.Tables(0).Rows(0)("WORK_STATE") I tried If IsDbNull(dsPrsn.Tables(0).Rows(0)("WORK_STATE")) Then it seems not too work. I am attempting to check this field...
15
by: TC | last post by:
What does it mean for an integer to have a null value? I am trying to use the DataView.Find method. That method has an integer return type which contains the "index of the row in the DataView...
13
by: Jiho Han | last post by:
Here's the issue. You have a class, Class Person { public int id; public string firstname; public string lastname; }
4
by: Eric Layman | last post by:
Hi everyone, Im puzzled by a NULL behaviour in SQL 2000 server. There is a column in the table that does not allow NULL. During data mining, the staff noted that, for that particular column,...
2
by: nologo | last post by:
Hi, Visual Studio 2005, C#.net I wish to return a NULL value from a date control if no date is selected. Anyone got any idea on how i would do this? Cheers
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.