Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
How To Replace NULL Values in Expressions using ISNULL()?
Submitted by: AdministratorAs you learned from provious 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 GlobalGuideLineDatabase;
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 becaus
Submitted by: Administrator
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 GlobalGuideLineDatabase;
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 becaus
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.