how-to-filter-for-sql-null-or-empty-string

SQL Null or Empty How to Check for Null or Empty Column in SQL Server SQL

A Not Null  in a database really means the lack of a value. It is a special “value” that you can’t compare to using the normal operators. You have to use a clause in SQL IS Null.

On the other hand, an empty string is an actual value that can be compared to in a database. You simply use two ticks together.

Create Table First in SQL Server

CREATE TABLE [dbo].[Employee](

      [Employee_id] [int] IDENTITY(1,1) NOT NULL,

      [First_name] [nvarchar](300) NULL,

      [Last_name] [nvarchar](300) NULL,

      [Salary] [decimal](20, 2) NULL,

      [Joining_date] [datetime] NULL,

      [Department] [nvarchar](200) NULL

) ON [PRIMARY]

 Insert Record 
 

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('John', 'Abraham', 1000000.00, 1905-06-07, 'Banking')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Roy', 'Thomas', 700000.00, 1894-06-27, 'Banking')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Tom', 'Jose', 600000.00, 1894-06-27, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Jerry', 'Pinto', 650000.00, 1894-06-27, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Philip', 'Mathew', 750000.00, 1894-06-28, 'Services')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('TestName1', '123', 650000.00, 1894-06-28, 'Services')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('TestName2', 'Lname%', 600000.00, 1894-06-27, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Yamuna', 'Singh',4000.00, NULL, NULL)

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Pankaj', 'Roy', 45000.00, NULL, '')

Show Record

select * from [dbo].[Employee]

 

In this above table, I specifically put in some Department that are both null and empty strings.

Get the NULL values using the IS NULL operator.

select * from [dbo].[Employee]

where [Department] is null

Result

Get the empty string using the empty string.

select * from [dbo].[Employee]

where [Department] = ''

Result

 

  If you want to combine them to search for the SQL null or empty string together and retrieve all of the empty strings and nulls all at once, you could do something like this.

select * from [dbo].[Employee]

where [Department] = ''

or [Department] is null

Result