Is it good practice to trim whitespace (leading an

Question

Presuming that the spaces are not important in a field's data, is it good practice to trim off the spaces when inserting, updating or selecting data from the table ?

I imagine different databases implement handling of spaces differently, so to avoid that headache, I'm thinking I should disallow leading and trailing spaces in any field data.

What do you think?

Answer 1

If leading and trailing spaces are unimportant, then I'd trim them off before inserting or updating. There should then be no unnecessary spaces on a select.

This brings some advantages. Less space required in a row means that potentially more rows can exist in a data page which leads to faster data retrieval (less to retrieve). Also, you are not constantly trimming data on SELECTs. (Uses the DRY [don't repeat yourself] principle here)

Answer 2

Trailing spaces are particularly problematic, specifically in regards ANSI_NULLS behaviour.

For instance, colname = '1' can return true where colname like '1' returns false

Thus, given trailing spaces in varchar columns are ambiguous, truncation is most likely preferable, particularly because there is no real information in such data and it creates ambiguity in the behaviour of SQL Server.

For example, look at the discussion at this question:

Why would SqlServer select statement select rows which match and rows which match and have trailing spaces

Answer 3

I think it is a good practice. There are few things more soul crushing than spending an hour, a day, or any amount of time, chasing down a bug that ultimately was caused by a user typing an extra space. That extra space can cause reports to go subtly wrong, or can cause an exception somewhere in your program, and unless you have put brackets around every print statement in your logs and error messages, you might not realize that it is there. Even if you religiously trim spaces before using data you've pulled from the db, do future users of your data a favor and trim before putting it in.

Answer 4

I would trim them (unless you are actually using the whitespace data), simply because it is easy to do, and spaces are particularly hard to spot if they do cause problem in your code.

Answer 5

Handling trailing spaces is a good practise. It is a common mistake in databases and it leads to long searching of mistakes.

Either trim them during insert/ update, or add a check clause to your table like this:

ALTER TABLE tblData
WITH CHECK ADD  CONSTRAINT CK_Spaces_tblData 
CHECK 
(
    datalength(USERID)>(0) 
    AND datalength(ltrim(rtrim(USERID)))=datalength(USERID) 
)

In this case, users get an error when they try to insert or update.

This has the advantage, that users know about the mistake. Very often, they already have trailing spaces in some Excel sheet, and then they copy-paste. So it's good for them to know about this, so they can remove the error also in their excel sheets.

Answer 6

I would say it's a good practice in most scenarios. If you can confidently say that data is worthless, and the cost of removing it is minimal, then remove it.