Need kb values from mb 1

[Shrum]

Somebody has to have to run into this before...

I've a db in which file sizes are stored in what I call least-form:

9kb, 1mb, 2gb

vs a similiar-qualified form:

9000, 1000000, 1000000000

Has anybody come up with a evaluation function that would turn something like '1.3mb' -> 1300000?

BG: I'm trying to run AVG()'s off the file sizes

TIA

=============================
Sean Shrum
Shrum Consulting

http://consulting.shrum.net

 

[r937]

here is the test data i used:

[tt]insert into alphanumerics (alphanum)
values ('12kb')
, ('120 mb ')
, ('200mb ')
, ('11000kb')
, ('3gb')
, ('5 gb ');[/tt]

here is my test query:

[tt]select alphanum
, left(alphanum,length(trim(alphanum))-2)
, right(trim(alphanum),2)
, case
when right(trim(alphanum),2) = 'kb'
then format(1000*
left(alphanum,length(trim(alphanum))-2),0)
when right(trim(alphanum),2) = 'mb'
then format(1000000*
left(alphanum,length(trim(alphanum))-2),0)
when right(trim(alphanum),2) = 'gb'
then format(1000000000*
left(alphanum,length(trim(alphanum))-2),0)
end
from alphanumerics
where right(trim(alphanum),2)
in ('kb','mb','gb')[/tt]

here are the results:

[tt]12kb 12 kb 12,000
120 mb 120 mb 120,000,000
200mb 200 mb 200,000,000
11000kb 11000 kb 11,000,000
3gb 3 gb 3,000,000,000
5 gb 5 gb 5,000,000,000[/tt]


rudy

http://r937.com/