Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Query to delete null or empty values from datatable in sql server

Jul 9, 2012
Introduction:

Here I will explain how to write a query to delete null or empty values from datatable in SQL Server.

Description:

In previous articles I explained many articles relating to SQL Server. Now I will explain how to write a query to delete null or empty values from datatable in SQL Server. For that first design one table (UserInfo) in database and enter data like as shown below

UserId
Name
City
Education
1
Suresh
Guntur
B.Tech
2
Nagaraju
NULL
MCA
3
Sai
MBA
4
Madhav
NULL
MBBS
Query to get records without null or empty values

If we want to get the records without null or empty values we need to write query like as shown below


SELECT UserId,Name,City,Education FROM UserInfo WHERE City IS NOT NULL AND City <>''
Output

UserId
Name
City
Education
1
Suresh
Guntur
B.Tech
Query to get records with null or empty values

If you want get records where City contains NULL or empty you need to write query like this


SELECT UserId,Name,City,Education FROM UserInfo WHERE City IS NULL OR City =''
Output

UserId
Name
City
Education
2
Nagaraju
NULL
MCA
3
Sai
MBA
4
Madhav
NULL
MBBS
Query to delete null or empty values from datatable

If you want to delete null or empty records from datatable we need to write query like this


DELETE FROM UserInfo where City IS NULL OR City =''
Output

UserId
Name
City
Education
1
Suresh
Guntur
B.Tech

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

4 comments :

Kedar said...

You have written query for delete as "DELECTE FROM UserInfo where City IS NULL OR City =''"

But that should be like "DELETE FROM UserInfo where City IS NULL OR City =''"

I think this is spelling mistake.

Suresh Dasari said...

@Kedar...
Thanks for notify my spelling mistake.

Unknown said...

Hi Suresh .... I am inserting a new record to database and binding it to grid view.... but in grid view along with new record null record for all columns are inserted .. So can u guide me to remove the null records... thanks in advance

Unknown said...

Thnx Sir

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.