Northwind Pivot issue

I tried using some code for the new pivot function in the "Inside Microsoft SQL Server 2005 T-SQL Querying" book.

I was using the Northwind database but that was from a SQL 2000 database. There was an error that stated the database needed to be updated.

I have looked all over the web and have not seen where to download the SQL 2005 Northwind & Pubs database.

Anybody have the URL for this or is this not available?

Thanks,

Gene

You can download the scripts here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Denis the SQL Menace

http://sqlservercode.blogspot.com/

None aggregation function

I have created a cube where I simply want to store non-aggregatable values. I have used the none aggregation function setting but can't seem to browse the leaf level and see any values after processing. If I set the aggregation function to sum , max or min, all works as expected. I assume that None should be used when you want to load non-aggreatable data into a cube. I would have expected to be able to see data at the leaf level, is this assumption incorrect? I'm using SQL 2005 SP1.

None aggregation function works, you probably simply are not browsing at the real leaves. It is not that simply to get to real leaves, since you need to position every single attribute included in the measure group to get there. For testing you can use Leaves() function (few more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx), but with just a browser you will have hard time navigating to real leaves.|||

Mosha-

Thanks for the rapid response! You are correct I had a tough time getting to the leaves. Problem was... I used the upper filter area in the VS browser, rather that the filters directly on the top of the grid, and each view gave a differing response. I have put screen shots into a word doc to demonstrate but can't find a way to attach them to this post. At least my question is anwsered but I'm slighly concernerd that I got 2 different results based on the way I filtered the view.

Thanks Again

Chris

Non-Deterministic function in DPV

Hi! Using the new SQL 2005 functionality where the optimizer determines
where the data is based on the check constraints defined on the tables, can
you use a non-deterministic function such as getdate() in the check
constraints as long as you have another mechanism to make certain the data
gets moved when the date is no longer in the valid range.
For example: Table 1 has data that is 12 months old or less. Table 2 has
data that is 13 months old or more.
Thanks.alw (alw@.discussions.microsoft.com) writes:
> Hi! Using the new SQL 2005 functionality where the optimizer determines
> where the data is based on the check constraints defined on the tables,
> can you use a non-deterministic function such as getdate() in the check
> constraints as long as you have another mechanism to make certain the
> data gets moved when the date is no longer in the valid range.
> For example: Table 1 has data that is 12 months old or less. Table 2 has
> data that is 13 months old or more.
No. It simply does not make any sense. SQL Server cannot trust you to
actually use that mechanism. If you want to move data daily, then you
have to redefine the constraints daily. Not to talk about if your datetime
column also has minutes and milliseconds...
Note that in SQL 2005 you also have partitioned tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

NON_EMPTY_BEHAVIOR if NON EMPTY isn't used

A cube I'm working on has lots of calculations. But none of the MDX queries in the reports use the NON EMPTY clause or the NonEmpty function. What I'm wondering is whether the optimizer or any other part of the Formula Engine uses NON_EMPTY_BEHAVIOR for anything else besides removing empty cells from the resultset.

If the Sum function is summing two calculated measures, will a NEB on those two calculated measures help the Sum function, for example? That's the kind of thing I'm looking for... places where NEB is used other than removing entire cells from the resultset.

Yes, NON_EMPTY_BEHAVIOR is used even if there are no NON EMPTY queries. Some more information about it here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx|||

Mosha, thanks for the reply.

The takeaway from your blog regarding NEBs is that the multiplication operator (as in the * operator used for math, not for crossjoin) uses the NEB. Are there any others?

What I was hoping for was sort of a list of places where NEB's are used unless that's an enormous list.

|||

Multiplication was one of the examples. Many other (but not all) MDX functions also take it into account. The exact list is not easy to build, because there are some other additional factors affecting whether or not NEB will be used.

The most important thing though, is to ensure that NEB is used correctly, before thinking about perf. Even in this forum, we routinely see people using NEB incorrectly, which can and does cause wrong results.

Non deterministic function?

Gentlemen What is "Non_Deterministic" about the function below?

I pass DATETIME Column and a DECIMAL column ti the function. It keeps yelling at me saying it is a non-deterministic function.

I am using this function to PERSIST a Computed Column.

I have tried converting all NVARCHARs to VARCHARs.

Tredi returning a VARCHAR instead of a DATETIME, but still did not succeed.

Am I doing something wrong, I must be.....

CREATE FUNCTION [dbo].[udf_GetDateTime](@.Date DATETIME, @.TimeDecimal DECIMAL)

RETURNS DATETIME

AS

BEGIN

DECLARE @.DateStr NVARCHAR(23)

DECLARE @.TimeStr NVARCHAR(12)

DECLARE @.DateTimeResult DATETIME

SET @.TimeStr = RIGHT('000000' + CONVERT(NVARCHAR(6), @.TimeDecimal), 6)

SET @.DateStr = CONVERT(NVARCHAR(10), @.Date, 120) + ' ' +

SUBSTRING(@.TimeStr, 1, 2) + ':' +

SUBSTRING(@.TimeStr, 3, 2) + ':' +

SUBSTRING(@.TimeStr, 5, 2)

RETURN CONVERT(DATETIME, @.DateStr, 120)

END

I've tried on my sql servers, 2000 and 2005, and worked correctly on both of them.
The code I used to test your udf (with Management studio) is the following:

declare @.d datetime
set @.d = getdate()
print dbo.[udf_GetDateTime] (@.d, 1)|||

Could you please explain Carlop -- what you are trying to convey.

So you think the above function is Deterministic Or Non-Deterministic.

I know it works, but when I use the function to create a Computed Column (persisted), it yells saying I cannot do that -- since the function is non-deterministic.

|||

It is not deterministic because the UDF is not created with the SCHEMABINDING option. Without this option, the database engine has no way of knowing if the definition of the module has changed after it was created and so on. You can check deterministic property of modules by using OBJECTPROPERTY(OBJECT_ID('<your_module>'), 'IsDeterministic'). So create the function with SCHEMABINDING to make it deterministic.

Also, it appears that the use of scalar UDF in this case is unnecessary. You can simply use an expression to get the result you need in a computed column. Note that using scalar UDFs for simple operations has lot of overhead and hurts performance. So you will be better off using expression in computed column directly.

|||If you have to wrap the function up you are best to use a SQLCLR function. These perform better that TSQL UDFs, however as has been stated, bot perform worse than using an expression directly.|||Sorry but I didn't noticed that you used it to persist a column, so I've misunderstood the usage...

Nominal intrest in TSQL

Hi

I have to make a calculation based on the Excel Nominal function to get the nomanal Interest

Month Quarter Half year per year 2% = 0,16516% 0,49629% 0,99505% 2,00% 3% " 0,24663% 0,74171% 1,48892% 3,00% 4% " 0,32737% 0,98534% 1,98039% 4,00% 5% " 0,40741% 1,22722% 2,46951% 5,00% 6% " 0,48676% 1,46738% 2,95630% 6,00% 7% " 0,56541% 1,70585% 3,44080% 7,00% 8% " 0,64340% 1,94265% 3,92305% 8,00% 9% " 0,72073% 2,17782% 4,40307% 9,00% 10% " 0,79741% 2,41137% 4,88088% 10,00% 11% " 0,87346% 2,64333% 5,35654% 11,00% 12% " 0,94888% 2,87373% 5,83005% 12,00%

In Exell the formula goes like this:
Per Month =NOMINAL(aIntrest;12)/12
Per Quarter =NOMINAL(aIntrest;4)/4
Half Year =NOMINAL(aIntrest;2)/2
Per Year =NOMINAL(aIntrest;1)

I would be very happy to get help on this.

I'm not entirely sure, if Excel's NOMINAL is working that way, however you can use the following to get the same results

DECLARE @.apr FLOAT
DECLARE @.frequency FLOAT

SET @.apr = 0.02
SET @.frequency = 12
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Monthly
SET @.frequency = 4
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Quarterly
SET @.frequency = 2
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Semiannual
SET @.frequency = 1
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Annual
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

Super

Thanks, so simple

Nominal intrest in TSQL

Hi

I have to make a calculation based on the Excel Nominal function to get the nomanal Interest

Month

Quarter

Half year

per year

2%

=

0,16516%

0,49629%

0,99505%

2,00%

3%

"

0,24663%

0,74171%

1,48892%

3,00%

4%

"

0,32737%

0,98534%

1,98039%

4,00%

5%

"

0,40741%

1,22722%

2,46951%

5,00%

6%

"

0,48676%

1,46738%

2,95630%

6,00%

7%

"

0,56541%

1,70585%

3,44080%

7,00%

8%

"

0,64340%

1,94265%

3,92305%

8,00%

9%

"

0,72073%

2,17782%

4,40307%

9,00%

10%

"

0,79741%

2,41137%

4,88088%

10,00%

11%

"

0,87346%

2,64333%

5,35654%

11,00%

12%

"

0,94888%

2,87373%

5,83005%

12,00%

In Exell the formula goes like this:
Per Month =NOMINAL(aIntrest;12)/12
Per Quarter =NOMINAL(aIntrest;4)/4
Half Year =NOMINAL(aIntrest;2)/2
Per Year =NOMINAL(aIntrest;1)

I would be very happy to get help on this.

I'm not entirely sure, if Excel's NOMINAL is working that way, however you can use the following to get the same results

DECLARE @.apr FLOAT
DECLARE @.frequency FLOAT

SET @.apr = 0.02
SET @.frequency = 12
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Monthly
SET @.frequency = 4
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Quarterly
SET @.frequency = 2
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Semiannual
SET @.frequency = 1
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Annual
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

Super

Thanks, so simple

No Trim Function?

Hi,
Is there an equivalent to the TRIM() function in SQL Server to remove any
leading or trailing blank spaces in a string variable?
Please advise.
Thanks,
-JoeSELECT LTRIM(RTRIM(' foo '))
"Joe Rigley" <jcrigley@.spartanmotors.com> wrote in message
news:eXkEC0jrFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there an equivalent to the TRIM() function in SQL Server to remove any
> leading or trailing blank spaces in a string variable?
> Please advise.
> Thanks,
> -Joe
>|||RTRIM / LTRIM (look in the BOL)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe Rigley" wrote:

> Hi,
> Is there an equivalent to the TRIM() function in SQL Server to remove any
> leading or trailing blank spaces in a string variable?
> Please advise.
> Thanks,
> -Joe
>
>|||Ahh! Thanks much!
BOL? What is that and how do I locate?
-Joe
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:FBEB9AF1-211B-4B5B-8572-9D00F041C791@.microsoft.com...
> RTRIM / LTRIM (look in the BOL)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Joe Rigley" wrote:
>|||BOL = Books Online.
http://www.aspfaq.com/2229
"Joe Rigley" <jcrigley@.spartanmotors.com> wrote in message
news:usv1t%23jrFHA.3796@.TK2MSFTNGP11.phx.gbl...
> Ahh! Thanks much!
> BOL? What is that and how do I locate?
> -Joe
> "Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message
> news:FBEB9AF1-211B-4B5B-8572-9D00F041C791@.microsoft.com...
>

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.

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

|||

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.

|||

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

|||

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!

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

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

|||

Yes there should be a TRIM function. What a Joke.

"NNTP User" sounds like a communist.

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

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.

|||

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.

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

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?

|||

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

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.

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

|||

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.

|||

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

|||

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!

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

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

|||

Yes there should be a TRIM function. What a Joke.

"NNTP User" sounds like a communist.

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

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.

|||

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.

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

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?

|||

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

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.

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

|||

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.

|||

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

|||

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!

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

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

|||

Yes there should be a TRIM function. What a Joke.

"NNTP User" sounds like a communist.

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

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.

|||

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.

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

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?

|||

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

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.

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

|||

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.

|||

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

|||

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!

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

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

|||

Yes there should be a TRIM function. What a Joke.

"NNTP User" sounds like a communist.

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

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.

|||

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.

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

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?

|||

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

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.

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

|||

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.

|||

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

|||

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!

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

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

|||

Yes there should be a TRIM function. What a Joke.

"NNTP User" sounds like a communist.

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

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.

|||

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.

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

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?

|||

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

No Transaction is Active

Hi,
I have a COM+ Application written by somebody else, and when I am calling
one function, it is returning an "No Transaction Is Active". I have looked a
t
the code, and cannot see why. I have the same version of the dll running
perfectly fine on other servers, so I cannot see that it is the code that is
at fault, so I am left to believe that it is being returned by SQL Server
(SQL Server 2000, SP4).
Can anyone help explain what the error message means and why it is coming up
?
Regards
SamHi
Are you getting error numbers 3902/3903? In which case you are trying to
ROLLBACK/COMMIT without a BEGIN TRANSACTION. You may want to use SQL Profile
r
to see where this is occuring.
e.g. instances such as
http://support.microsoft.com/defaul...kb;en-us;231987
http://support.microsoft.com/defaul...kb;en-us;223213
John
"Sam Coleman" wrote:

> Hi,
> I have a COM+ Application written by somebody else, and when I am calling
> one function, it is returning an "No Transaction Is Active". I have looked
at
> the code, and cannot see why. I have the same version of the dll running
> perfectly fine on other servers, so I cannot see that it is the code that
is
> at fault, so I am left to believe that it is being returned by SQL Server
> (SQL Server 2000, SP4).
> Can anyone help explain what the error message means and why it is coming
up?
> Regards
> Sam