Replacing NULL and Empty string in Select statement

Welcome to Appsloveworld Technologies, In this session, we learn about the different ways that are available to replace Null values and SQL server. Specifically will be looking at ISNULL() function, case statement, and COALESCE() function.

Now I have a TblEmployeeManager which has got three columns, EmpId, name, and Manager_Id.

Now if I ask you who is the manager of Jackson, you’ll look at the Manager_Id column. Manager_Id for Jackson’s 6 and you look up 6 within the EmpId column,6 is Gabriel. So Jackson’s manager is Gabriel.

So along the same lines, if you look at Anthony, Manager_Id value is NULL, which means Anthony doesn’t have a manager.

And if you remember in our SQL joins series we have written a left outer self join to retrieve employee names along with their manager names.Since Anthony doesn’t have a manager ID, his manager’s name is Null.

Any employee, if he doesn’t have a manager, doesn’t display Null replace those null values with this no manager word. Is this possible? Absolutely Yes, We have different ways to do that.

I don’t want null to be displayed instead of null, I want to replace that with ‘Super Boss’.

We can make use of ISNULL(),case statement and COALESCE() functions. Let us look at practical examples of using each one of them.

All right. So since we are using this Manager_Id column and then looking up in the EmpId column, we’re kind of doing a selfie join here.

And if you’re not sure about what a self-join is, please read the below post.

• What is SELF JOIN ? with Realtime Example

So let’s right the left outer self join Query.

SELECT Emp.EmployeeName as Employee, Mng.EmployeeName as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

when we execute the above query, we get the below result.But what’s our ultimate aim? Our ultimate aim is basically to replace the null value with the ‘Super Boss’ word.

So how do we do that? the easiest way to do it is basically to use ISNULL() function.

SELECT Emp.EmployeeName as Employee, ISNULL(Mng.EmployeeName,' Super Boss') as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

Now ISNULL() is a simple function, now as the name itself suggests ISNULL if it is NULL.

The first parameter, it’s an expression that you pass in. If the expression that you’re passing in, if it returns null, then there is a second parameter called replacement value.

Along the same lines, you can actually use COALESCE() function as well. So another way to replace the null value is to use COALESCE() function

if you look up the definition of COALESCE() function in MSDN books online, it says it returns the first non-null value. What do we mean by that? the usage of the COALESCE() function is pretty much similar to the ISNULL() function, but there is much more to it.

it is basically used to return the first non-null value, very powerful function.you can read more about COALESCE() in the below post.

SELECT Emp.EmployeeName as Employee, COALESCE(Mng.EmployeeName,' Super Boss') as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

COALESCE() is much more powerful than ISNULL(). You can pass and multiple expressions to that and it returns the first non-null expression value.

The final way to use the case statement. now you have a case statement is also very simple,

SELECT Emp.EmployeeName as Employee, CASE WHEN Mng.EmployeeName IS NULL then 'Supper Boss' ELSE Mng.EmployeeName END as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

Table Sql Script

GO
/****** Object:  Table [dbo].[TblEmployeeManager]    Script Date: 10/12/2020 9:41:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblEmployeeManager](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [nvarchar](max) NULL,
    [Manager_Id] [int] NULL,
 CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED 
(
    [EmpId] ASC
)
) 
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON 
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF
GO

The post Replacing NULL and Empty string in Select statement appeared first on Software Development | Programming Tutorials.