Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of experience. Tim is the principal of Tyleris Data Solutions and is a Linchpin People teammate. Tim has spoken at international, regional, and local venues including the SQL PASS Summit, SQLBits, SQL Connections, SQL Saturday events, and various user groups and webcasts. He is a board member at the North Texas SQL Server User Group in the Dallas area. Tim is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2. You can visit his website and blog at TimMitchell.net or follow him on Twitter at twitter.com/Tim_Mitchell.

Give Us TRIM()!

If you're like me, you've probably typed in LTRIM(RTRIM([value])) hundreds, if not thousands, of times during your travels through SQL Server.  I've long wondered why there was no integrated function that would serve this purpose.

Pinal Dave (@pinaldave) has submitted an item on Microsoft Connect to suggest the TRIM() function as an addition to Transact-SQL.  He correctly points out that most programming languages have this feature baked in.

So if you're on board with the movement to add TRIM() to T-SQL, log in to Microsoft Connect and add your vote and comments.

Comments

Posted by mhotek on 28 May 2009

LOL!  Do you know how many decades that has been on the feature request list?  It was there for several years before Microsoft licensed the first version from Sybase and has been requested hundreds of times on a yearly basis ever since then.  This onbe falls into the same category as compressed backups.  There was a great big deal made out of the fact that you can FINALLY compress backups in SQL Server 2008, my response to that was "It's about time, it only took over 2 decades to get it done."  So, I'm certainly not going to hold my breath that this will ever make it into the product either, too many other "important" things like Notification Services take priority over the stuff that millions of people have asked for.

Posted by Michelle Ufford on 29 May 2009

Thanks, Tim!  I just voted.  :)

Posted by Aaron Alton on 30 May 2009

I suspect that the lack of a "Trim()" function is a result of the fact that left trimming renders an the index on a given column unusable.  If there were a "Trim()", most developers would use it all the time, even when Rtrim() is all that is required.

How often do you really use LTrim()?  I use it very infrequently, whereas RTrim() is fairly common.

Posted by Craig Greenwood on 1 June 2009

Such trimmage should be done when putting data into the database, on the application side so that minimal trimmage is necessary on the db side.  That said it would be nice, but I agree with Aaron.  If such a shortcut existed people would use it when it is not necessary.  On the other hand, that's not exactly horror on the level of Jack the Ripper or Ted Bundy.  I like the point that other programs have included it as old-hat for years.  So I'm stumped.  I don't use either much in my work.  To Bill Gates, I say, "Add it!"  To the rest of us, enjoy thy ltrim(rtrim()).

How 'bout this for further discussion: Which is more correct: ltrim(rtrim()) or rtrim(ltrim())?  Whew! That should get a nice civil war going!

Craig

Posted by Tim Mitchell on 1 June 2009

I agree that LTRIM(RTRIM()) should not be necessary, in a perfect world.  The data in the datatabase should be cleaned such that these functions are not required.  However, I find in my travels that I often run into databases where I need to retrieve the data for some purpose, but cleaning the data (including removing extraneous spaces) is outside the scope of my project or authority.  A trivial change, to be sure, but a do-it-all TRIM function would be a nice addition to the T-SQL language.

Posted by deanroush on 1 June 2009

I can see it both ways in terms of convenience vs necessity.  I occasionally have users that inadvertently hit the space bar when beginning to enter data in a field, and it is imperceptible to them depending on the font involved.  So I call LTRIM(RTRIM()) on all text entry just to be sure it enters the database in a clean state.

If this seems like extra work I guess you could always create a UDF named TRIM().

Posted by PaulHunter on 1 June 2009

Aaron,

Just because somebody might misuse something isn't a reason to deny it to all.  I'd like to have a TRIM() for data / parameter cleaning before use, so ...what part of the index will be missed?

Posted by rajeshsivadasan on 2 June 2009

I agree with deanroush, probably microsoft wants people to be more creative and use it's features like UDF's ;)

Posted by obrieste on 2 June 2009

That’s the same reason why Jap cars are far better than American.

1) The Jap's fix all the little problems.  Hence no big problems arise.

2) The Americans come up with some big new feature, use it to push sales.

Sql server should have

1) trim,

2) create alter statement together

3) More thank one identity column on a table

4) Some form of sequence for auto numbering across tables and partitions.

5) Materialised Views.  Indexed Views are almost unusable as you have so many stupid settings and limits.

Posted by Aaron Alton on 2 June 2009

@PaulHunter Agreed, but I still think that in this particular case, it's best left out.  For parameter scrubbing it would be fine - you'll only get into trouble when people do something like this:

WHEN Trim(MyField) = @SomeConstant

I think that the temptation would be too great to use Trim(), and the unsuspecting user wouldn't understand that the LTrim() component of Trim() is killing their index use.

Leave a Comment

Please register or log in to leave a comment.