Is it good practice to trim whitespace (leading and trailing) when selecting/inserting/updating table field data?

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

For typical data enty it's not worth the overhead. Is there some reason you think you are going to get lots of extra blank lines? If you are then it might be a good idea to trim to keep DB size down but otherwise no.

Answer 2

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 3

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.

Answer 4

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 5

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 6

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:

http://stackoverflow.com/questions/1143313/why-would-sqlserver-select-statement-select-rows-which-match-and-rows-which-match/1143324#1143324