Null Processing in MS SQL Server 2008

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  Compa开发者_如何学CnyName 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?

wrap COALESCE around columns where you want blanks

COALESCE(MiddleInitial,'') AS MiddleInitial   

repeat for the other columns

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

COALESCE(FieldThatMayBeNull, '') as FieldThatMayBeNull

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

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, 

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 ;

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