ISNULL() - Replacing NULL Values in Expressions in SQL Server
How To Replace NULL Values in Expressions using ISNULL() in SQL Server Transact-SQL?
As you learned from previous tutorials, NULL values presented in expressions will cause the final results to be NULL. Sometimes, you want NULL values to be replaced with some default values, like 0, '', or 'NULL', so that expressions can be evaluated properly.
SQL Server offers a built-in function called ISNULL() to help you replacing NULL values in expressions:
ISNULL(expression, replacement) -- Returns "expression", if it is not NULL -- Returns "replacement", if "expression" is NULL
The tutorial example below shows you how to replace possible NULL values in @middle_initial:
USE FyiCenterData; GO CREATE PROCEDURE welcome @first_name VARCHAR(20), @middle_initial VARCHAR(1), @last_name VARCHAR(20) AS PRINT 'Hello '+@first_name + ' '+@middle_initial + ' '+@last_name; PRINT 'Hello '+@first_name + ' '+ISNULL(@middle_initial,'') + ' '+@last_name; GO EXEC welcome 'John', 'W', 'King'; GO Hello John W King Hello John W King EXEC welcome 'John', NULL, 'King'; GO Hello John King
The first PRINT statement in the second test returns a blank line because of NULL value.
2017-02-03, 1833👍, 0💬
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...
How To Display a Past Time in Days, Hours and Minutes in MySQL? You have seen a lots of Websites are...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...