Archives

Drawing a Blank. Dealing With Blank Values in SQL Server using NullIf and Coalesce

“Melodical Murray is a human musical instrument; he makes music by blowing his…blank” – Match Game

In the previous article I discussed the issues when working with Null-able fields in Joins and Where clauses. This article will deal with the problems of blank values.

What About Blanks?

Many systems forego the use of Null-able fields, but instead use blank values. In fact, blank values are a bigger problem in M2M than Nulls. A common problem is with M2M Product Class values. For those that don’t use M2M, Product Class is similar to a Product Category that happens to be tied to general ledger accounts. Unfortunately, not every record in sales and other tables has a Product Class value and the value is blank. If you weren’t aware of this, run the following query against your M2M Database.

SELECT DISTINCT ARI.fprodcl ProductClass
FROM   dbo.aritem ARI

The M2M Educational Database returns the following:

In order to demonstrate this for those who don’t use M2M, I’ll run the following against the Kung Fu Database.

UPDATE dbo.OrderDetails
SET    ItemDescription = ''
WHERE  orderid = 4
 
SELECT DISTINCT OD.ItemDescription
FROM dbo.OrderDetails OD


So, how can we deal with the blank value (and the Null value as well?) Well, we could use a Case statement.

 SELECT DISTINCT
       CASE
         WHEN OD.ItemDescription IS NULL THEN '..Null None'
         WHEN OD.ItemDescription = '' THEN '..Blank None'
         ELSE OD.ItemDescription
       END
         AS ItemDescription
  FROM dbo.OrderDetails OD


Noticed that I assigned the value to Null and blank separately so you could see that it worked. Why did I use two periods before the text? I’ll explain that in a minute. The Case When works, but it’s wordy. There’s a much easier way to deal with both Blanks and Nulls.

NullIf and Coalesce

Let’s start with the NULLIF function. NULLIF accepts two arguments and if the arguments are the same, then Null is returned, otherwise the first value is returned. Therefore, we can detect blanks with the following:

 
SELECT NULLIF ('A', '')
 -- Returns 'A'
SELECT NULLIF ('', '') 
-- Returns Null
Declare @Test Char(5)
SELECT NULLIF (@Test, '') 
-- Returns Null
-- I had to use a variable for the last test because the first argument must 
-- have a known data type or an error will result.

The second argument in this case is always ” (blank). Why? If the field value is blank, the end result will be Null. Also, if the first argument is Null, that will return as Null as well. Make sense?

A Coalesce statement can then be used to replace the resulting Null value like so:

SELECT COALESCE(NULLIF ('A', '') , '..None') 
-- Returns 'A'
SELECT COALESCE(NULLIF ('', '') , '..None') 
--Returns '..None' 
Declare @Test Char(5)
SELECT  COALESCE(NULLIF (@Test, '') , '..None') 
-- Returns '..None'

Let’s return to our original statement and substitute this method.

SELECT DISTINCT
       coalesce (nullif (OD.ItemDescription, ''), '..None')
         AS ItemDescription
  FROM dbo.OrderDetails OD

I have this process programmed as a Toad Toad for SQL Server snippet. When I have time I’ll record a video showing how to use them. In this case, all I have to do is highlight the database field name, click the snippet, and Toad does the rest. So, why do I substitute text with leading periods? I do that primarily for generating available values in SSRS parameters.

When I go back and edit the first script (for M2M Product Classes) with this method, I get the following:

SELECT DISTINCT
       ARI.fprodcl ProductClass
      ,coalesce (nullif (ARI.fprodcl, ''), '..None') FixedProductClass
  FROM dbo.aritem ARI
ORDER BY coalesce (nullif (ARI.fprodcl, ''), '..None')


Which would your users rather see in a SSRS parameter drop down?

It’s important to note that blank (”) is an actual value, which is in fact equal to another blank, and can be used to join as well. However, in the case of Made2Manage, you shouldn’t expect to join your tables to the Product Class Table (INProd) with an Inner Join because you’ll “lose” records. That’s why I always link to INProd with a Left Outer Join. Would you like to see the invoice records that you’d lose in an Inner Join? Run the following against your database to find out.

SELECT ARM.fbcompany AS BillToCompany
      ,ARI.fcinvoice AS InvoiceNo
      ,ARI.fitem AS ItemNo
      ,ARI.fprodcl AS ProductClass
      ,rtrim (ARI.fpartno) AS PartNo
      ,ARI.frev AS PartRev
      ,ARI.ftotprice AS NetExtInvoiced
  FROM   dbo.armast ARM
       INNER JOIN
         dbo.aritem ARI
       ON ARM.FCINVOICE = ARI.FCINVOICE
 WHERE ARM.fcstatus <> 'V' AND
 
       coalesce (nullif (ARI.fprodcl, ''), '..None') = '..None' -- Returns Blanks and Nulls

In the M2M Educational Database, 19 records are returned.

So, in these two articles, I’ve demonstrated the problems of dealing with Nulls and Blanks in your database and how to deal with them. In the future, I’ll follow up with articles on creating and using Date tables and other more advanced T-SQL techniques.

Stay tuned!

Related posts:

8 comments to Drawing a Blank. Dealing With Blank Values in SQL Server using NullIf and Coalesce

  • Hmm, something I’ve never used before.

    Nice work, David.

  • Glenn

    Building on your example… Since you know the ARITEM table does not always have a Product Class, you may want to get the Product Class from the SOITEM table.

    If the item invoiced doesn’t exist on a SO, you may then want to get the Product Class from the Item Master.

    If it’s a non-standard part, then the Product Class won’t exist there either. Building on your example, the query might be something like…

    select nullif(iAR.fProdCl,”) AR_PC,
    iSO.fProdCl SO_PC,
    Inv.fProdCl IM_PC,
    coalesce(nullif(iAR.fProdCl,”), iSO.fProdCl, Inv.fProdCl, ‘..NONE’) PC
    from ArItem iAR
    left join SoRels rSO on rSO.fSoNo + rSO.fINumber + rSO.fRelease = iAR.fSokey
    left join SoItem iSO on iSO.fSoNo = rSO.fSoNo and iSO.fINumber = rSO.fINumber
    left join InMast Inv on Inv.fPartNo = iAR.fPartNo and Inv.fRev = iAR.fRev and Inv.Fac = iAR.Fac

    The first three fields are included only for validation purposes. Building on your previous article regarding ISNULL and COALESCE, this uses the COALESCE function to get the first non null value of 4 choices. This will get the not blank Product Class from the ARITEM table, if it is null then it will get the Product Class from the SOITEM table, if it is null then it will get the Product Class from the INMAST table, if this is null then it will assign ‘..NONE’ to the PC field.

    An additional note regarding ISNULL() verses COALESCE()…
    If you use SELECT COALESCE(NULLIF (”, ”) , ‘-None’) the result will be “-NONE’
    if you use SELECT ISNULL (NULLIF (”, ”) , ‘-None’) the result will be “-”
    So the use of COALESCE() is again preferred over ISNULL()

    These results were obtained using SQL 2008 R2.

  • Any tips on using this in conjunction with the pivot and dynamic headers?

  • Jen, I’m not sure exactly what you mean. I use this code when pulling information for pivots and for PowerPivot for that matter. What exactly are you asking?

  • I think I know how, but it’d be messy, so I’m hoping my idea isn’t the only way to do this…

    I’ve found a lot of examples like:

    SELECT Isnull([0] , 0) as Column1,
    Isnull([1] , 0) as As Colum2 ,
    Isnull([2],,0) as Column3
    and So oN….

    FROM

    (SELECT Column1, Column2 FROM YourTableName) AS TableName

    PIVOT

    (

    AVG( Column2 )

    FOR Column1 IN ([0], [1], [2], [4])

    ) AS PivotTable;

    But since I don’t know the column name in advance, I’m not sure of a simple way to do this…unless I want to go back into the string I use to form the header titles and pull them out (that’s the route I’ll go otherwise). I mean, a super easy way would be something like ISNULL(Tablename.*,0) but life isn’t that simple 🙂

  • I hate to admit it but I don’t have a lot of experience with the Pivot function because most of my mission critical databases are in SQL 2K. I think you should ask that question at StackOverFlow.com or on Twitter with the #sqlhelp tag.

  • Thanks Dave, I appreciate it. It seems like one of those things that should be possible, but may be hairy to implement. It’s not at the top of my priority list right now since the client seems fine with the nulls, but I’d personally prefer it with the 0’s (and mainly, I want to use this as a learning opportunity).

    Speaking of learning new things…hadn’t occurred to me to use twitter for my questions. Great idea!

  • Thanks for the “case when null and when ”” idea.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>