none
No TRIM function in 2005 - are you serious!?

    General discussion

  • I cannot believe that there is yet another version of SQL Server without a TRIM function.  So for SQL 2005 (like I've done in 7.0 and 2000) my T-SQL will look like dog poo because I will have to write LTRIM(RTRIM(MyValue)) anytime I want to remove whitespace from both ends of character values instead of just writing TRIM(MyValue).

    Microsoft - how can you write LTRIM and RTRIM and not write TRIM in the same breath and how have you let it go so long without providing it.  This should have been in there since version 4.21.  I mean - how hard is it to add TRIM and just call LTRIM(RTRIM(Value)) under the hood.  Please give us this syntactic sugar in your first service pack for SQL 2005.

    Folks - please do not reply with workarounds (i.e. no UDFs or CLR recommendations).  There a several features that MS has neglected to put straight into 2005 (and prior versions for that matter) that must be there.  This is one of them.
    Thursday, November 10, 2005 12:36 PM

All replies

  • >I cannot believe that there is yet another version of SQL Server without
    > a TRIM function.  So for SQL 2005 (like I've done in 7.0 and 2000) my
    > T-SQL will look like dog poo because I will have to write
    > LTRIM(RTRIM(MyValue)) anytime I want to remove whitespace from both ends
    > of character values instead of just writing TRIM(MyValue).
    
    Why don't you create your own function called TRIM()?  You might also be 
    better served (and prevent your T-SQL from looking like "dog poo") by 
    trimming the data before it gets into the database in the first place... 
    then you don't have to worry about doing this at all...
    
    > Please give us this syntactic sugar in your first service pack for SQL 
    > 2005.
    
    Please do not encourage feature additions in service packs.  These are not 
    what service packs are for (though they have been abused for that purpose in 
    the past).
    
    > Folks - please do not reply with workarounds (i.e. no UDFs or CLR
    > recommendations).
    
    Then what is your point?  Send an e-mail to sqlwish@microsoft.com instead of 
    bothering people here... 
    
    
    

    Thursday, November 10, 2005 2:25 PM
  • Well thanks for that "no sh*t" solution ace.  Of course I trim data before it makes it to the database - FOR APPLICATIONS WHERE I HAVE CONTROL OF WRITING THE DATA ACCESS LAYER MYSELF!  But in this big ol' world of systems development you sometimes run into some "crazy" situations like third party apps and nightly DTS packages where someone left some dirt in the code and forgot to trim values.  Can you believe I have even had to contatenate a couple of character strings together in T-SQL (i.e. names) for things like data transfer objects and reports and had to trim things up a bit while chaining those strings together.  But maybe you haven't run into one of these "crazy" situations where a built in TRIM function would be handy.

    Of course I could write my own TRIM function, but I guess I'm just holding out for Microsoft to give me TRIM in T-SQL since they have given it to me in EVERY ONE OF THEIR OTHER LANGUAGES!  I guess I would expect that during a MS code review a development manager might see LTRIM and RTRIM and say "hmmm... why don't we also give 'em TRIM to knock that whitespace off the front and the back at the same time with only 4 characters and a set of parens."  Are you telling me that you wouldn't like to have TRIM too?

    There were only 2 options when posting - question and comment.  I looked for rant and it wasn't there so I chose comment instead.  I could have sent my rant to sqlwish (as I have before), but then again I could also have sent it to suggestionswelcome@thatbrickwalloverthere.com and gotten the same response.

    Instead what I think I'll do is just post the whole "LTRIM, RTRIM but no TRIM" debacle to The Daily WTF since this is truly what it is.

    Friday, November 11, 2005 2:13 AM
  • > Well thanks for that "no sh*t" solution ace.
    
    Really not surprised by the response.  Just another troll looking for a 
    place to b*tch?
    
    > situations like third party apps and nightly DTS packages where someone
    > left some dirt in the code and forgot to trim values.
    
    And your biggest concern is having to type a few extra letters to trim both 
    sides of the data?  Have you considered using varchar instead of char, then 
    you could just use LTRIM() -- just one more character than TRIM()!
    
    > maybe you haven't run into one of these "crazy" situations where a built
    > in TRIM function would be handy.
    
    I guess I just haven't really gotten as workd up as you wish to be over the 
    sheer agony of typing LTRIM(RTRIM()) instead of TRIM() in those rare 
    occasions where it is actually necessary.  I guess I spend a hell of a lot 
    more time fixing designs built by people seeking shortcuts than worrying 
    about using those shortcuts myself.
    
    > Of course I could write my own TRIM function, but I guess I'm just
    > holding out for Microsoft to give me TRIM in T-SQL
    
    So hold out.  You spent about 1000 times more energy b*tching about it here 
    and wasting people's time than you would have had you just written the 
    function and gotten over it.  Or just continued to wrap your horrible data 
    in LTRIM() and RTRIM().  Oh, the horror.
    
    > Are you telling me that you wouldn't
    > like to have TRIM too?
    
    I really couldn't care less, because those extra few keystrokes are not 
    worth giving myself a hernia over.
    
    > There were only 2 options when posting - question and comment.
    
    You should try NNTP instead of a lousy web-based newsreader.  For one, it 
    doesn't make you choose between these two narrow categories, which those of 
    us using NNTP like it was meant to be used can't see anyway.
    
    > for rant and it wasn't there so I chose comment instead.  I could have
    > sent my rant to sqlwish (as I have before), but then again I could also
    > have sent it to suggestionswelcome@thatbrickwalloverthere.com and gotten
    > the same response.
    
    You probably won't believe me, but e-mails sent to sqlwish are actually 
    read, considered and weighted.
    
    > Instead what I think I'll do is just post the whole LTRIM, RTRIM but no
    > TRIM to The Daily WTF since this is truly what it is.
    
    All right, have fun with that.  Still not sure what you were trying to 
    accomplish by posting here.  If you want to rant, get a blog.  This 
    community is supposed to be for peer to peer technical assistance, not for 
    outright complaining with no interest in solutions or workarounds, IMHO. 
    
    
    

    Friday, November 11, 2005 3:09 AM
  • How can you dare to say "bothering people here... ".
    BAnVA is asking very important and correctly stated question.
    If you have no answer, just don’t interfere!


    Friday, November 11, 2005 8:37 PM
  • I have to agree, having TRIM seems obvious when you have LTRIM and RTRIM.  That's like having the notion of a front door and a back door, but no notion of a door in general.  Anyway, VARCHAR is not necessarily the answer either.  I've encountered situations where a value from a CHAR datatype is loaded into a VARCHAR datatype via DTS and the trailing spaces remain even though it's in a VARCHAR datatype.  This was SQL2k, so maybe that's not an issue in SQL 2005. 
    Saturday, November 12, 2005 12:30 AM
  • How is he trolling? Do you know what trolling is? Really you’re just a pedant. It's easy to minimize someone else's frustration with simple solutions and sarcasm like "...sheer agony of typing LTRIM(RTRIM()) instead of TRIM()".

    Ultimately yes you're right it's not *that* big of a deal, but he raises a good point. Plus when you throw in other functions and you're dealing with a lot of parenthesis...

    Saturday, November 12, 2005 1:12 AM
  • Yes there should be a TRIM function. What a Joke.

    "NNTP User" sounds like a communist.

    Saturday, November 12, 2005 6:22 PM
  • "Why don't you create your own function called TRIM()?"

    This is a good advice for those who play with home computers.
       Now imagine your code suppose to be executed on dozens databases and different servers. So if you are not the person responsible for data model you should notify DBAs and/or smbody else that you want to create this function, even such a simple one. It's possible but I hope MS could do it for us.
    Sunday, November 13, 2005 2:52 AM
  • Well I don't know if SQL2k5 does anything about it but making a cutom function just isn't the same since you ahve to preface it with the username that the function belongs to.

    It is a minor issue of course but I would agree that it is a major gripe.

    I myself ask the same question of why not provide the function  that would be sooooo easy to include.
    Monday, November 14, 2005 2:54 AM
  • Brick wall is an understatement. So what if the issue is minor? So is the fix, but they didn't do it. It is definitely annoying. Code readability is reduced. If there was no need for trim INSIDE T-sql, then why do we even have LTRIM and RTRIM?

    This is what trolling is:
    "NNTP from Microsoft, in an effort to eradicate untrimmed white spaces, officially advises all customers to stop using computers and to hand-write from now on. In a related matter NNTP pronounced that dog poo is now mandatory!"

     Mazz2000 wrote:
    "NNTP User" sounds like a communist.
    Even the commies had "Glasnost" in the end. A public forum is exactly the place and the way to voice a reaction to a missing feature. If enough people chime in it will get eventually included. 

    The issue is not trim() any more. This is a perfect example of a decaying and stagnant culture at MS, pervaded by the likes of NNTP. That is something to get worked up about.

    Tuesday, January 31, 2006 5:09 PM
  • I hope you are all finished with your yellings, at the end there is a solution that I posted some time ago:

    http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/78a5a709904a5f9d

    HTH, Jens Suessmeyer.

    Tuesday, January 31, 2006 6:55 PM
  • Well, thank you for showing us how to implement TRIM function, but that's not the issue here. Any decent programmer will know how to implement a TRIM function, just like he'll know how to implement AVG, SUM etc.

    Like Carl M. wrote, the issue is readability, especially when reading someone else's  code. Obviously it's much easier to read  TRIM(x) than LTRIM(RTRIM(x)) or something like SomeonesSuperFunction(x, ' '). Add a complex outer statement, and it becomes obvious why people are asking for TRIM.

    P

    Friday, May 11, 2007 9:44 AM
  • I agree

     

    How can SSRS has a TRIM function and SQL Server does not

    I'd take TRIM over LTRIM or RTRIM anyday Smile

     

    Let's hope SQL 2008 Katami will have it, PLEASE?

    Friday, May 11, 2007 2:50 PM
  • I agree too. This makes absolutely no sense. Microsoft alsways had the image of being inconsequent. But this is the best i've ever seen. So, dear microsoft, could you tell me why the hell SSRS has TRIM and the DB module does not??? Where is the missing logic? What makes you believe that the users of SSRS might want to use this function when they can also have LTRIM and RTRIM?

     

    Greets so far

    Monday, July 09, 2007 9:20 AM
  • SQLWish@microsoft.com has been deprecated.

     

    Use the following to offer product suggestions.

     

    Suggestions for SQL Server

    http://connect.microsoft.com/sqlserver

    Monday, July 09, 2007 3:25 PM