Null Processing in MS SQL Server 2008

Question

I’m not a SQL DBA and I’m having an issue. We run MS SQL 2008 server and just upgraded it from MS SQL 2000. I’m trying to run a query created for the DB in MS SQL 2000 and use the results for our mailing list.

The query is:

SELECT DISTINCT Title, FirstName, MiddleInitial, LastName, Suffix, 
CompanyName, BAddress, BAddress2, BCity, BState, BZip
FROM MailingExportView
WHERE     AdvanceMailing = 0 
ORDER BY BZip, LastName

Which gives us back something similar to:

Title   FirstName   MiddleInitial   LastName    Suffix  CompanyName BAddress    BAddress2   BCity   BState  BZip
Mr. Bob NULL    Smith   NULL    NULL    12345 Main , Anytown    State, NULL NULL

I’d like to not get the NULL. If the field is null ideally I’d like it to come back blank.

I’ve read about Null Processing from other sites and here;

http://technet.microsoft.com/en-us/library/ms170707.aspx

I’ve tried rearranging this so that the ISNULL will put the nulls in a different table:

How to replace blank (null ) values with 0 for all records?

http://bytes.com/topic/sql-server/answers/888443-convert-null-blank-if-not-null-remove-commas-string

http://www.w3schools.com/sql/sql_isnull.asp

I’m at a loss here. Can someone help me out?

4 COALESCE vs. 2 ISNULL, so take your pick. About the only differences are (1) coalesce will take any number of arguments and return the first in the list that is not null, and (2) isnull is a lot easier to type. — Philip Kelley
– Philip Kelley, Commented Sep 29, 2011 at 18:19

SQLMenace · Accepted Answer · 2011-09-29 18:08:24Z

2

wrap COALESCE around columns where you want blanks

COALESCE(MiddleInitial,'') AS MiddleInitial

repeat for the other columns

answered Sep 29, 2011 at 18:08

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JNK · Accepted Answer · 2011-09-29 18:08:38Z

2

If all you want is a blank instead of a null, try

COALESCE(FieldThatMayBeNull, '') as FieldThatMayBeNull

answered Sep 29, 2011 at 18:08

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Comments

Joe · Accepted Answer · 2011-09-29 18:08:47Z

1

Change the select to ISNULL(Column, '') for any potentially null field...

SELECT DISTINCT 
     ISNULL(Title, '') AS Title, 
     ISNULL(FirstName, '') FirstName, 
     ISNULL(MiddleInitial, '') MiddleInitial, 
     ISNULL(LastName, '') LastName, 
     ISNULL(Suffix, '') Suffix, 
     ISNULL(CompanyName, '') CompanyName, 
     ISNULL(BAddress, '') BAddress, 
     ISNULL(BAddress2, '') BAddress2, 
     ISNULL(BCity, '') BCity, 
     ISNULL(BState, '') BState, 
     ISNULL(BZip, '') BZip
FROM MailingExportView
WHERE     AdvanceMailing = 0 
ORDER BY BZip, LastName

answered Sep 29, 2011 at 18:08

Joe

42.8k20 gold badges108 silver badges129 bronze badges

Comments

Conrad Frix · Accepted Answer · 2011-09-29 18:14:23Z

1

Without showing us what you tried its unlcear what your problem is.

That said there are two standard ways of converting a null to a default value on a select. ISNULL and COALESCE. CASE is also an option and its over the top but I included it anyway

SELECT DISTINCT 
   Title, 
   FirstName, 
   COALESCE(MiddleInitial, '') MiddleInitial, 
   LastName, 
   ISNULL(Suffix,'') Suffix, 
   CompanyName, 
   CASE WHEN BAddress IS NULL THEN '' ELSE BAddress END BAddress,

answered Sep 29, 2011 at 18:14

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Comments

nvogel · Accepted Answer · 2011-09-29 18:11:42Z

0

SELECT DISTINCT
COALESCE(Title,'') Title,
COALESCE(FirstName,'') FirstName,
COALESCE(MiddleInitial,'') MiddleInitial,
COALESCE(LastName,'') LastName,
COALESCE(Suffix,'') Suffix,
COALESCE(CompanyName,'') CompanyName,
COALESCE(BAddress,'') Baddress,
COALESCE(BAddress2,'') Baddress2,
COALESCE(BCity,'') BCity,
COALESCE(BState,'') BState,
COALESCE(BZip,'') BZip
FROM MailingExportView
WHERE AdvanceMailing = 0 
ORDER BY BZip, LastName ;

answered Sep 29, 2011 at 18:11

nvogel

25.6k3 gold badges47 silver badges89 bronze badges

Comments

BenMorel · Accepted Answer · 2014-01-08 12:38:45Z

0

I guess you could use ISNULL() function in your case similar to something below:

    SELECT DISTINCT ISNULL(Title, ''), ISNULL(FirstName, ''), ISNULL(MiddleInitial, ''), ISNULL(LastName, ''), ISNULL(Suffix, ''),  ISNULL(CompanyName, ''), ISNULL(BAddress, ''), ISNULL(BAddress2, ''), ISNULL(BCity, ''), ISNULL(BState, ''), ISNULL(BZip, '') FROM MailingExportView WHERE     AdvanceMailing = 0  ORDER BY BZip, LastName

edited Jan 8, 2014 at 12:38

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Sep 29, 2011 at 18:10

Praveen

1,44916 silver badges25 bronze badges

Collectives™ on Stack Overflow

Null Processing in MS SQL Server 2008

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related