Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

SQL Calculations issue

by DrAxeman (Beadle)
on Aug 10, 2005 at 20:52 UTC ( #482758=perlquestion: print w/ replies, xml ) Need Help??
DrAxeman has asked for the wisdom of the Perl Monks concerning the following question:

I'm having trouble getting proper averages calculated in my tables. All of the data in my table (a CSV file) is wrapped in quotes. For DBI/SQL to calculate the average on the column, I need to remove the quotes. No biggie, except that some data is in scientific notation. When I do my averages, I'm getting outrageous numbers.

Starting data
"5.3271433710011766e-006","5.3271433710011766e-006"

After I strip the "'s and -'s I've got

5.3271433710011766e006,5.3271433710011766e006

The true value of these numbers is

0.00000532714337100118,0.00000532714337100118

How can I get these properly calculated, or should I just grep for these types of numbers and change the value to 0 since they are so small?

Also, if I were to modify the value to 0, what is the regex that would say "if there is an e in the word, change the whole word to something else"?

Comment on SQL Calculations issue
Select or Download Code
Re: SQL Calculations issue
by AReed (Pilgrim) on Aug 10, 2005 at 21:03 UTC
    If you strip the '-' you're changing the exponent from a negative number to a positive number. The result is that what was a very small number is now very large. You should be able to just strip the quotes and perform your calculation.
      Excellent! It worked. Early in the process I was having problems with come non-alphanumeric characters, so I just stripped them all. Thanks!
Re: SQL Calculations issue
by sgifford (Prior) on Aug 10, 2005 at 21:05 UTC
    Perl seems to understand scientific notation when converting strings to numbers. I think your problem is stripping the minus signs. That means you turn, for example, 3.4e-2 (.034) into 3.4e2 (340). If you just leave the minus sign in place, I think you'll get correct results.

    Also, consider using a CSV module from CPAN for this; it can take care of most of the parsing for you.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://482758]
Approved by sgifford
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2014-12-20 08:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (95 votes), past polls