Benefits to NOT trimming strings of leading and trailing white spaces in database applications

Question

I am not a developer, but I have spent years testing and managing software projects. Bugs caused by leading and trailing white space in strings are like war wounds that will never completely heal.

So I ask, in what situations are leading and trailing white spaces beneficial?

Answer 1

Benefits to NOT trimming strings of leading and trailing white spaces in database applications

NONE.

all data should be formatted consistently in the database. this prevents every query having to code for all possible exceptions.

When storing in fixed length string data types like CHAR, all data is stored consistently (fixed length), thus this is not an exception, and meets my above rule. To expand on Neil Butterworth answer that CHAR is sometimes faster. it depends on the length, CHAR(5) might have an advantage, because it is easier for the DB predict rows per page, and find the row in the page. However CHAR(2000) is terrible compared to varchar(2000), you'll have many fewer rows per page.

Answer 2

I think a better question is why isn't it standard practice to enforce this constraint or create custom data types at the database level.

Probably because it's more development work up front, and it's an easy thing to skip.

Answer 3

Markdown uses two spaces at the end of a line as a marker for saying that that newline should be turned into a <br>. :-P

Seriously, beyond such corner cases, I don't really see a point to keeping leading/trailing spaces around. YMMV. :-)

Answer 4

Speed. Using the CHAR datatype (which preserves trailing spaces) will often be faster than VARCHARS. Having said that, when I come across a database that uses the CHAR type, I go looking for its designer with an axe - the speed difference almost never compensates for the difficulty of using the CHAR type.