The Daily WTF: Curious Perversions in Information Technology

icelava · 2005-02-14 Reply Admin

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

fluffy · 2005-02-14 Reply Admin

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

2005-02-14 Reply Admin

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

Mike R · 2005-02-14 Reply Admin

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

2005-02-14 Reply Admin

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

JamesCurran · 2005-02-14 Reply Admin

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

Jeff S · 2005-02-14 Reply Admin

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

Obviously, they gave up on this function and aren't using it, which is good, so hopefully someone smartened up and said "you know, we could do formatting at the ... I don't know ... PRESENTATION layer?! "

People who write functions like this are the same people who wonder why dates and dollar amounts don't sort correctly in their SQL statements. ("why does the stupid computer think my varchar value of $9.20 is larger than $123.43 ? Or that 1/2/2004 comes BEFORE 5/12/2002? Stupid computers!")

fuali · 2005-02-14 Reply Admin

well, I bet this is a helluva performance increase!

Bustaz Kool · 2005-02-14 Reply Admin

Jeff S:

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

Amen, brother... Testify!!!

Blue · 2005-02-14 Reply Admin

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

Mike Dimmick · 2005-02-14 Reply Admin

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]

Jeff S · 2005-02-14 Reply Admin

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.

EWilson · 2005-02-14 Reply Admin

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.

2005-02-14 Reply Admin

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.

EWilson · 2005-02-14 Reply Admin

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

JamesCurran · 2005-02-14 Reply Admin

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"

Son of Guayo · 2005-02-14 Reply Admin

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

Jon Limjap · 2005-02-14 Reply Admin

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

EWilson · 2005-02-15 Reply Admin

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

EWilson · 2005-02-15 Reply Admin

[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. :)

2005-02-15 Reply Admin

Another question about the logo's.

Does 03 13 37 have some special meaning??

2005-02-15 Reply Admin

here we go...finest hack0r slang:
http://en.wikipedia.org/wiki/Leet

2005-02-18 Reply Admin

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

???

2008-03-29 Reply Admin

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)

2009-12-02 Reply Admin

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.

De-evolutional Theory

Leave a comment on “De-evolutional Theory”