SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server vs Oracle: How to replace NULL values

It’s Wednesday and that means another SQL/Oracle post. Today we’ll be discussing NULL Values, which can sometimes be a real pain. Don’t worry though there’s a simple solution. Simply replace the NULL value with another.

Comparing a column with NULL and replacing with another value is really simple. There are built in functions for replacing NULL values. I’m not going to discuss the ANSI standard COALESCE here. If you want to know more about it you can find it on Bing.

SQL Server

SQL Server’s built in function for replacing NULL values is named ISNULL(). This very simple function takes two parameters, tests the first for NULL and if NULL is found substitutes the second parameter for the first.

SELECT ProductID, Weight, ISNULL(Weight, 0.00)
FROM Production.Product

Result

1, NULL, 0.00

2, NULL, 0.00

SELECT ISNULL(NULL, ‘XYZ’)

Result

XYZ

With the ISNULL function you can pass NULL as the first parameter; though, I’m not sure why you would want to.

SQL Server 2012 added the IIF function which works just like the MS Office IIF. Though I don’t see this widely used. Typically you’ll see ISNULL and COALESCE.

Oracle

The Oracle function may not be the one you’d think of first. Oracle has a function named NULLIF and it does something completely different. NULLIF compares to values and if they are the same returns NULL.

Replacing NULL values in Oracle is done with the NVL function, which works the same as SQL Server’s ISNULL described above.

Pass a column and if the column is NULL the function will return the value of the second parameter.

It’s worth mentioning that Oracle also has a function named NVL2 which allows you to pass in 3 parameters. The first parameter is compared and if NULL then the 3rd parameter is returned and if not NULL then the 2nd parameter is returned.

Conclusion

Both RDBMS platforms have the ANSI standard function for comparing and replacing NULL values (COALESCE). Both platforms have two native functions for this task that behave in nearly the same manor. So if you’re switching between the two or learning one or the other, this isn’t an area you need to worry about.

Confessions of a Microsoft Addict

Daniel Janik has been supporting SQL Server for 18 years as a DBA, developer, architect, and consultant. He spent six years at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has spoken at several SQL Saturday events across the US and Caribbean and regularly speaks at PASS local chapters.

Comments

Leave a comment on the original post [sqltechblog.com, opens in a new window]

Loading comments...