473,554 Members | 3,217 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.membe rAddress + ' ' + tblMember.membe rTown + ' ' +
tblMember.membe rCounty + ' ' + tblMember.membe rPostCode + '<br> ' +
tblMember.membe rCountry + '<br> ' + tblMember.membe rInstitution AS
concatAddress FROM tblMember WHERE memberSurname=' Cardy'

returns a null value if eg tblMember.membe rInstitution 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 27742

"John Morgan" <jf*@XXwoodland er.co.uk> wrote in message
news:i7******** *************** *********@4ax.c om...
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.membe rAddress + ' ' + tblMember.membe rTown + ' ' +
tblMember.membe rCounty + ' ' + tblMember.membe rPostCode + '<br> ' +
tblMember.membe rCountry + '<br> ' + tblMember.membe rInstitution AS
concatAddress FROM tblMember WHERE memberSurname=' Cardy'

returns a null value if eg tblMember.membe rInstitution 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_YIE LDS_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*@XXwoodland er.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
5395
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 indicating null); $var3 = (something indicating null); This would be useful as I have form fields that are not required and
3
1151
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 + ' ' + tblMember.memberTown + ' ' + tblMember.memberCounty + ' ' + tblMember.memberPostCode + '<br> ' + tblMember.memberCountry + '<br> ' +...
2
1950
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 PBV_rstCompany.Fields("Installer") <> "Y" Then txtInstaller.Visible = False lblInstallerLBL.Visible = False cmdNextInstaller.Visible = False
3
7059
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 parameter classes and call the webservice just fine. Our problem is, within our .Net app, we have some value objects ( like floats, for instance...
2
1765
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 in my data set to see if it is null or not. If it is null I want to set my drop down list to a certain value (where I'm using dropdown.SelectedValue...
15
29217
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 containing the sort key value specified; otherwise a null value if the sort key value does not exist." By "null value", does it mean System.DBNull? (I...
13
1744
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
2120
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, there are a few records that are empty. I do not specifically know whether they are "alt + 0160" character.
2
2771
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
7615
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7539
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7819
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7581
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7897
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5446
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5165
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2030
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
858
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.