Auto trim Database entries?

Question

Is it possible edit a table definition 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??

Answer 1

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

Answer 2

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.

Answer 3

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.