Comment On De-evolutional Theory

Is there a CalculatePriceOfCar() UDF that returns $1.11 too? I'd love to buy from this system.

What's wrong with just storing the number as a float or fixed-point decimal or whatever?

Regarding the logo voting: The URL for the 6th logo has the same URL as the 5th option.

One would think if they were about to give up, they'd just return @MoneyAmount.



I'd take a stab at pointing out a short and proper implementation, but,
I have no idea what the original intent of the function may have
been.  Why would someone want to change 1.00 to 1, leave 1.100 as
1.100 and 1.11 as 1.11?



I mean if the intent were to return 1.00 as 1, 1.100 as 1.1 and 1.11 as 1.11, you'd think they'd do something like  cast(cast('1.0' as float) as varchar(10))

:

Regarding the logo voting: The URL for the 6th logo has the same URL as the 5th option.

If you want to vote for 6, then this should work...

So, is this what they wanted....

CREATE FUNCTION ufTrimZeros_JMC (@MoneyAmount varchar(15)) 
RETURNS varchar(15) AS 
BEGIN
DECLARE @ma2 varchar(18)
-- Assumes the near certainty that the string !@! does not appear in the
-- @moneyAmount to start with
 SET @ma2 = @MoneyAmount + '!@!'
 SET @ma2 =  REPLACE( @ma2, '.00!@!', '!@!')
 SET @ma2 =  REPLACE( @ma2, '.0!@!', '!@!')
 SET @ma2 =  REPLACE( @ma2, '.!@!', '!@!')
 SET @ma2 =  REPLACE( @ma2, '!@!', '')
 
 RETURN @ma2
END

well, I bet this is a helluva performance increase!

Jeff S:

Formatting should never be done by the database layer.  This is horrible. 

 

Amen, brother... Testify!!!

I particularly like the pinky-finger / inkpad thing.  Subtle way
of saying you didn't go out of your way to prevent cheating.



I'm guessing some will happen, given my experience in the online gaming
world, and observation of emerging democracies (hell, even
long-established ones!)

All the messing around with REPLACE is crazy. It wastes memory. Instead you should start at the right-hand end of the string, gather the contiguous set of zeros, then check if there's a decimal point to the left of the zeros. If there is, trim off the zeros using SUBSTRING. If not, do nothing - the zeros are significant.

I'd say it's crazy to do this in the database, but I know of a product that forces it (or so it seems) due to its lame formatting capabilities: Crystal Reports.

Sample code, if you really must:

[code language="tsql"]

DECLARE @MoneyVariable varchar(15)
SET @MoneyVariable = '1.1100000'
 

DECLARE @ret varchar(15)
 

DECLARE @firstZero int
SET @firstZero = LEN(@MoneyVariable) + 1
 

WHILE @firstZero > 0
BEGIN
    IF 

SUBSTRING(@MoneyVariable, @firstZero - 1, 1) <> '0' 

BREAK
    SET @firstZero = @firstZero - 1
END
 

-- @firstZero now points to the first zero. Check if there's a
-- decimal 

point before this point
 

DECLARE @p int
SET @p = @firstZero - 1
 

DECLARE @found bit
SET @found = 0
 

WHILE @p > 0
BEGIN
    IF SUBSTRING(@MoneyVariable, 

@p, 1) = '.' 
    BEGIN 


        SET @found = 

1
        BREAK
    END
    SET @p = @p - 1
END
 

IF @found = 1
    SET @ret = SUBSTRING(@MoneyVariable, 1, 

@firstZero - 1)
ELSE
    SET @ret = @MoneyVariable
 

SELECT @ret
[/code]

Mike Dimmick:

I'd say it's crazy to do this in the database, but I know of a product that forces it (or so it seems) due to its lame formatting capabilities: Crystal Reports.

Huh ?  We have our first "WTF" comment of the day!

Obviously, like almost ALL presentation layer applications, Crystal will automatically trim trailing zeroes from numbers -- assuming you actually return a correct (numeric) datatype.  And, of course, if you want to format the number any other way (or have crystal convert the varchar to a numeric type) it is pretty easy to do if you know how read the manual or the help files.

Please, for your sake and the sake those who someday will inherit the code you write, learn how to use a product before you start calling it "lame" and begin inventing horrible unnecessary "work-arounds" such as coding functions like this.

I agree with other comments that this should (generally) be a presenation tier issue. However, some of the suggested "solutions" are also not much better than the original failed attempts, IMHO. This seems simple and efficient:

return left(@MoneyVariable, patindex('%[^.0]%', reverse(@MoneyVariable)))

(Find the right-most non-0 and non-decimal-point character, and give me back everything left of that.)

Sometimes the data tier is the *only* tier (as in data-warehousing and data extracts) and it's handy knowing what little tricks T-SQL has to offer.

Dear non-denominational-deity -- I think the code in that comment is actually worse than the original WTF!  Well, ok, maybe only almost worse.  It's hard to tell, but it's right up there anyway.

(One addendum... this would leave the decimal there if it was all zeros to the right. This could be wrapped in similar logic to trim the right-most character if it ended being a decimal point. It can still stay a one-liner, which SQL Server can better "inline" into queries for performance.)

EWilson:

This seems simple and efficient:

return left(@MoneyVariable, patindex('%[^.0]%', reverse(@MoneyVariable)))

"simple and efficient" and well, wrong. First of all, patindex will return the index into the reversed string, while left() needs the index into the forward string, but thats simple enough to fix with a "len(@MoneyVariable) - " in there.

But the real problem is that it doesn't stop at the decimal.  Given the string "120.00", yours would reduce that to just "12"

EWilson:

(Find the right-most non-0 and non-decimal-point character, and give me back everything left of that.)

Well, this would trim 1,000,000 down to 1 - at least the original would only trim it down to 1.11 :)

I think the function should have been named "I_Give_Up" [:P]

Well, this would trim 1,000,000 down to 1 - at least the original would only trim it down to 1.11 :)
[/quote]

Yes, it unfortunately assumed an incoming string *with* a decimal. The point was not to deliver a full-fledged function but to illustrate that there was (probably) a solution that could be done in one line. (I think it's safe to say if anyone uses code verbatim from a web-site, esp. a forum, that's a major WTF in itself, right?)

But... lesson learned: don't post things typed up in 90 seconds with no testing while trying to simultaneously put on coat to get out the door for a doctor's appointment. :)

[Prior reply mangled in forum for some reason...]

Son of Guayo:

Yes, it unfortunately _ass_umed an incoming string *with* a decimal. The point was not to deliver a full-fledged function but to illustrate that there was (probably) a solution that could be done in one line rather than N pages. (I think it's safe to say if anyone uses code verbatim from a web-site, esp. a forum, that's a major WTF in itself, right?)

But... lesson learned: don't post things typed up in 90 seconds with no testing while trying to simultaneously put on coat to get out the door for a doctor's appointment. :)

Another question about the logo's.

Does 03 13 37 have some special meaning??

here we go...finest hack0r slang:

http://en.wikipedia.org/wiki/Leet

EWilson:

I agree with other comments that this should
(generally) be a presenation tier issue. However, some of the suggested
"solutions" are also not much better than the original failed attempts,
IMHO. This seems simple and efficient:

return left(@MoneyVariable, patindex('%[^.0]%', reverse(@MoneyVariable)))

(Find the right-most non-0 and non-decimal-point character, and give me back everything left of that.)

So, for an input value of

12300000.00



we could expect an output value of

123



???

Could you just do something like this?

DECLARE @retVal int
SET @retVal = SELECT cast(@floatVal as Integer)
return @retVal

Rather than complex Regex statements? I mean, that's if you REALLY need to format something here as opposed to in your actual presentation layer.

(please excuse any syntax errors, don't do much T-SQL work)

The "keep all old/irrelevant code, just comment it out" method of version control is one I'm very familiar with in some of the legacy code I maintain.

Michele Watches
http://replica038.com/michele-watch-watches.html

Jacob & Co. Watches
http://replica038.com/Jacob-Co-Watches.html