Auto trim Database entries?

Is it possible edit a table d开发者_StackOverflow中文版efinition to automagically trim entries of all trailing whitespace when they are entered?

Or would this be more efficient/easier/normally done in the code befeore submitting the entries??

If you are working within SQL Server you can make this automatic but you should only really do this if you are sure and with appropriate consideration. The ANSI_PADDING decides in SQL as to whether it trims the trailing spaces of a value being inserted. The setting is applied at the time that table is created, and is not altered after, and the setting will work for varchar, but does not work for nvarchar.

A test script to show the difference is as follows:

set ansi_padding on
create table foo (myID int identity, myString varchar(50))
insert into foo values ('abcd      ')
select datalength(mystring) from foo

drop table foo
set ansi_padding off
create table foo (myID int identity, myString varchar(50))
insert into foo values ('abcd      ')
select datalength(mystring) from foo

On the first table the data length returns as 10 since the spaces were inserted, on the second example they are trimmed on insert.

I would personally prefer the code controlling what was needed to be done instead of leaving it to the setting, but I included the examples to show it can be done at the DB level if required.

You could possibly use a trigger - depending on what database software you're using.

Stored procedure or in client code or in triggers etc

SET ANSI_PADDING will always be ON in future, and it must be ON for some indexes too.

You can use Trigger INSTEAD OF INSERT Section. INSTEAD OF INSERT, you can trim value and try to insert it. If you specify field as unique index it should throw an exception because of duplicate index value.