SQL Server - Remove Null rows from a Table - Asked By Jr .net on 05-Feb-08 08:54 PM

I want to delete a complete row where one of its column value is NULL, I tried like

Delete from MyTable where  FirstName is NULL  or ' ' 

Advance thnx


give this query a try for your deletion of rows with column values=null

sundar k replied to Jr .net on 05-Feb-08 11:11 PM

Try this query

delete from MyTable where FirstName is NULL or len(rtrim(ltrim(FirstName)))=0

NULLIF - F Cali replied to Jr .net on 05-Feb-08 11:04 PM

You can also use the NULLIF function for this:

DELETE FROM [dbo].[MyTable] WHERE NULLIF(RTRIM([FirstName]), '') IS NULL

How do you get Null values at the first place - Nenad Prekupec replied to Jr .net on 06-Feb-08 03:19 AM

As it seems to me you are doing things on the wrong way.
Data base itself has menas to protect from such kind of things automaticly, defining constraints at database level will give you less work for keeping your DB clean of such things, the only thing you have to do is handle errors from DB (this is what you have to do anyway) and you have to say what fields in tables you don't want to have NULL values, that way it's impossible to have your situation in the first place.