No TRIM function in 2005 - are you serious!?

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.

Answer this question

No TRIM function in 2005 - are you serious!?

Annie24372

> 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.

gpspilot
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...
Rich Hanbidge
Yes there should be a TRIM function. What a Joke.

"NNTP User" sounds like a communist.
micwazoo

I agree

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

I'd take TRIM over LTRIM or RTRIM anyday

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

zmy

>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...

claudia galimberti
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.
kryzchek
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
Voodoo
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
Sergey Barskiy
"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.
Donna M-T

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!< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
pallavi
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.
keajatthew
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.
lololon
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.
DaSteph
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.
No TRIM function in 2005 - are you serious!?

Answer this question