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

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

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


The true value of these numbers is


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

Replies are listed 'Best First'.
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?

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

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (10)
As of 2016-08-26 13:56 GMT
Find Nodes?
    Voting Booth?
    The best thing I ever won in a lottery was:

    Results (371 votes). Check out past polls.