Solved

Issues with NULL

Posted on 2010-09-01
20
404 Views
Last Modified: 2012-05-10
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
Comment
Question by:Planmaster
  • 7
  • 3
  • 2
  • +4
20 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 125 total points
ID: 33578391

Select 

Name,

(isnull(Address1, '') + ',' + isnull(Address2,'') + ',' + isnull(Address3,'') + ',' + isnull(Posttown,'')) AS Address,

Mobile_number

From Table1

Open in new window

0
 
LVL 6

Assisted Solution

by:Jerryuk007
Jerryuk007 earned 125 total points
ID: 33578396
Use something like:

Select
isnull(Name,'') as [Name],
(isNull(Address1,'') + ',' + isNull(Address2,'') + ',' + isNull(Address3,'') + ',' + isNull(Posttown,'')) AS Address,
Mobile_number
From Table1
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33578400
>> 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
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33578426
you cannot add a null value to a string variable.
0
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 33578447
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
 

Expert Comment

by:truelight9
ID: 33578546
/*
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
 
LVL 41

Expert Comment

by:ralmada
ID: 33578592
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33582860
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 41

Expert Comment

by:ralmada
ID: 33587438
Well, I will have to object this. Jerryuk007 is exactly the same as mine. Can you please advise?
0
 
LVL 6

Expert Comment

by:Jerryuk007
ID: 33587489
@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
 
LVL 41

Expert Comment

by:ralmada
ID: 33587549
>>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
 
LVL 41

Expert Comment

by:ralmada
ID: 33589085
0
 
LVL 6

Expert Comment

by:Jerryuk007
ID: 33589185
Agreed with ralmada' suggestion.
0
 

Author Closing Comment

by:Planmaster
ID: 33627761
Apology for the late clearing.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33629321
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
 

Author Comment

by:Planmaster
ID: 33681818
Dear Experts,

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server query from excel 3 57
SQL Server stored proc 2 13
SQL Field Length for Email Address 3 17
SQL Server merge records in one table 2 12
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now