sql-is-not-null-and-is-not-empty-string

SQL Is Not Null or Not Empty 

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

A Not Null means the value in the column is not null (means it has some value in the column). You have to use a clause in SQL IS Not Null.

On the other hand, not 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 not null and not empty strings.

Get the Not NULL values using the IS Not NULL operator.

select * from [dbo].[Employee]

where [Department] is not null

Result

Get the not empty string using the not equal string.

select * from [dbo].[Employee]

where [Department] != ''

Result

  If you want to combine them to search for the SQL is not  null or not 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] IS NOT NULL) AND ([Department] != ''))

Result