How to convert empty value to nulll when insert or update

Posted on 2008-02-12
Medium Priority
Last Modified: 2008-09-20
in SQL Server 2005,I want to prevent any empty value in some columns in a table in updating or inserting and if user entered the empty value in a column it must convert to null instead
Question by:ali_alannah
LVL 60

Expert Comment

ID: 20876892
you could put a default value on the field...which would apply for inserts only, but you would have to write a function to do it and bind it to the column.  I dont' suggest this option.  I would write a trigger to do it.
LVL 39

Accepted Solution

BrandonGalderisi earned 2000 total points
ID: 20880999
A default value won't work because the default constraint is only used if a value is NOT specified.  Even if you supply NULL, it will use NULL.  My FIRST suggestion would be to tell whoever is inserting '' to stop.  Secondly.  If you must use trigger, you have to know whether or not you have existing triggers.  If you have existing triggers.  If you are dealing with inserts only (for now), the below should work.

The below example shows how it can be done if Field1 is your PK and Field3 is your problem field.
create trigger myIns_Trigger on myTable 
instead of insert
if @@rowcount=0
insert into myTable (field1, field2, field3)
select field1, field2, case field3 when '' then null else field3 end
from inserted
create trigger myUpd_Trigger on myTable
instead of update
if @@rowcount=0
update mt
set field2 = field2,
field3 = case field 3 when '' then null else field3 end
from myTable mt
join inserted i
on mt.field1 = i.field1

Open in new window


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month3 days, 3 hours left to enroll

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question