• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Issues with NULL

Experts,

I have an issue with NULL values in my database. I have in my contacts database, a table with columns

Address1, Address2, Address3, Posttown etc.

For a function I am trying to return the data using the following Select statement

Select
Name,
(Address1 + ',' + Address2 + ',' + Address3 + ',' + Posttown) AS Address,
Mobile_number
From Table1

I get the name and mobile number, and all the addresses where all the columns have been filled in.
But I don't get why, when one or more of the columns contain a null value, I get the whole thing returned as a null.

Can anyone help me out. Thanks in advance.

0
Planmaster
Asked:
Planmaster
  • 7
  • 3
  • 2
  • +4
2 Solutions
 
ralmadaCommented:

Select 
Name,
(isnull(Address1, '') + ',' + isnull(Address2,'') + ',' + isnull(Address3,'') + ',' + isnull(Posttown,'')) AS Address,
Mobile_number
From Table1

Open in new window

0
 
Jerryuk007Commented:
Use something like:

Select
isnull(Name,'') as [Name],
(isNull(Address1,'') + ',' + isNull(Address2,'') + ',' + isNull(Address3,'') + ',' + isNull(Posttown,'')) AS Address,
Mobile_number
From Table1
0
 
ralmadaCommented:
>> why, when one or more of the columns contain a null value, I get the whole thing returned as a null. <<
Because something + NULL = NULL
so you need to use the trick I've shown above

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jini Jose.Net Team LeadCommented:
you cannot add a null value to a string variable.
0
 
LCSandman8301Commented:
agreed the reason for this is because by adding to null the entire thing ends up null

however i would suggest a change in the is null to make the commas work correctly

try select (isnull(address1+',', '') + isnull(address2+',','') + isnull(address3+',','')+ isnull(posttown, ''))

otherwise the coalesce function could be used as well
0
 
truelight9Commented:
/*
Simply place this statement at the top of your script, before any Query or SELECT Statement
*/
SET CONCAT_NULL_YIELDS_NULL OFF

Select
Name,
(Address1 + ',' + Address2 + ',' + Address3 + ',' + Posttown) AS Address,
Mobile_number
From Table1
0
 
ralmadaCommented:
truelight9
with all due respect, but you should always try to avoid using that functionality.
Per BOL:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
http://msdn.microsoft.com/en-us/library/ms176056.aspx 
So you don't want to recommend something that will be removed...
0
 
Anthony PerkinsCommented:
gmailjini,

>>you cannot add a null value to a string variable.<<
Sure you can... You just may not get the result you expected, but there is nothing illegal about it.
0
 
ralmadaCommented:
Well, I will have to object this. Jerryuk007 is exactly the same as mine. Can you please advise?
0
 
Jerryuk007Commented:
@ralmada

As you can see from the submit time, we were typing the answer likely at the same time... Though my answer also took care of the first column being null even if that was not the requirements.

I will let the Question Author judge.

Jerry
0
 
ralmadaCommented:
>>Though my answer also took care of the first column being null even if that was not the requirements.<< You couldn't have put it more clear. Plus the author mentioned that he get correct results for the Name column, so the isnull on the Name column is completely unnecessary.
0
 
ralmadaCommented:
0
 
Jerryuk007Commented:
Agreed with ralmada' suggestion.
0
 
PlanmasterAuthor Commented:
Apology for the late clearing.
0
 
ralmadaCommented:
Planmaster, with all due respect but you stopped the autoclose process accepting a solution that I have already given to you. In addition, you have not indicated why you've chosen that answer (considering that it's the same as mine).
 
0
 
PlanmasterAuthor Commented:
Dear Experts,

I thought I had split the points. I do apologise for the oversight on my part.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now