CODEWITHSUNDEEP
sql-serverwhitespacetrim
Liao
Liao

Reputation: 1265

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

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?

Upvotes: 5

Views: 6434

Answers (7)

SQL Police
SQL Police

Reputation: 4206

Handling trailing spaces is a good practice. 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.

Upvotes: 1

polyglot
polyglot

Reputation: 2058

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

Upvotes: 0

Nico Burns
Nico Burns

Reputation: 17099

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.

Upvotes: 1

Peter Recore
Peter Recore

Reputation: 14187

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.

Upvotes: 5

Donnie DeBoer
Donnie DeBoer

Reputation: 2525

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.

Upvotes: 4

Colin Mackay
Colin Mackay

Reputation: 19175

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)

Upvotes: 4

Ogre Codes
Ogre Codes

Reputation: 19621

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.

Upvotes: 0

Related Questions