And its SQL query...with t as (select 'score' word, '*20+' num from dual union all
select 'hundred' , '*100+' from dual union all
select 'thousand', ')*1000+(' from dual union all
select 'lakh', ')*power(10,5)+(' from dual union all
select 'million', ')*power(10,6)+(' from dual union all
select 'crore', ')*power(10,7)+(' from dual union all
select 'billion', ')*power(10,9)+(' from dual union all
select 'trillion', ')*power(10,12)+(' from dual union all
select 'quadrillion', ')*power(10,15)+(' from dual union all
select 'quintillion', ')*power(10,18)+(' from dual union all
select 'sextillion', ')*power(10,21)+(' from dual union all
select 'septillion', ')*power(10,24)+(' from dual union all
select 'octillion', ')*power(10,27)+(' from dual union all
select 'nonillion', ')*power(10,30)+(' from dual union all
select 'undecillion', ')*power(10,36)+(' from dual union all
select 'duodecillion', ')*power(10,39)+(' from dual union all
select 'tredecillion', ')*power(10,42)+(' from dual union all
select 'quattuordecillion',')*power(10,45)+(' from dual union all
select 'quindecillion', ')*power(10,48)+(' from dual union all
select 'sexdecillion', ')*power(10,51)+(' from dual union all
select 'septendecillion', ')*power(10,54)+(' from dual union all
select 'octodecillion', ')*power(10,57)+(' from dual union all
select 'novemdecillion', ')*power(10,60)+(' from dual union all
select 'decillion', ')*power(10,33)+(' from dual union all
select 'vigintillion', ')*power(10,63)+(' from dual union all
select 'thirteen', '13' from dual union all
select 'fourteen', '14' from dual union all
select 'fifteen', '15' from dual union all
select 'sixteen', '16' from dual union all
select 'seventeen', '17' from dual union all
select 'eighteen', '18' from dual union all
select 'nineteen', '19' from dual union all
select 'twenty', '20+' from dual union all
select 'thirty', '30+' from dual union all
select 'forty', '40+' from dual union all
select 'fifty', '50+' from dual union all
select 'sixty', '60+' from dual union all
select 'seventy', '70+' from dual union all
select 'eighty', '80+' from dual union all
select 'ninety', '90+' from dual union all
select 'zero', '0+' from dual union all
select 'one', '1+' from dual union all
select 'two', '2+' from dual union all
select 'three', '3+' from dual union all
select 'four', '4+' from dual union all
select 'five', '5+' from dual union all
select 'six', '6+' from dual union all
select 'seven', '7+' from dual union all
select 'eight', '8+' from dual union all
select 'nine', '9+' from dual union all
select 'ten', '10+' from dual union all
select 'eleven', '11' from dual union all
select 'twelve', '12' from dual),
--
word_num as (select 'one hundred and fifty' a from dual union all
select 'one thousand and hundred' a from dual union all
select 'two lakhs fifty' from dual union all
select 'three crores six lakhs fifteen' from dual union all
select 'ONE THOUSAND - ONE HUNDRED - ELEVEN' from dual union all
select 'Four Thousand Four Hundred' from dual union all
select 'Five hundred and twenty seven' from dual union all
select 'Score And Seven' from dual union all
select to_Char(to_Date(trunc(dbms_random.value(1,5373484)),'j'), 'Jsp') from dual union all
select to_Char(to_Date(trunc(dbms_random.value(1,5373484)),'j'), 'Jsp') from dual union all
select to_Char(to_Date(trunc(dbms_random.value(1,5373484)),'j'), 'Jsp') from dual union all
select to_Char(to_Date(trunc(dbms_random.value(1,5373484)),'j'), 'Jsp') from dual union all
--end of test data
select text,
calculate_string(str) "number",
str string_for_calculating
from
(select text,
replace(replace(replace('('||regexp_replace(regexp_replace(a, '(power)|[[:alpha:] ]','\1'),'(\+)([^[:digit:](]|$)', '\2')||')','()*','(1)*'),'()','(0)'),'(*','(') str
from (select *
from (select rownum rn, a text, regexp_replace(lower(a),'[^[:alnum:]]',' ') a from word_num)
model
reference r
on (select rownum rn, word, num from t)
dimension by (rn)
measures(word, num)
main m
dimension by (rn dim)
measures (text, cast(a as varchar2(4000)) a)
rules iterate(1000) until(PRESENTV(r.word[iteration_number+1],1,0)<1)
(a[ANY]=regexp_replace(a[CV()], r.word[iteration_number+1], r.num[iteration_number+1]))
)
)
/
Anyways, I think the first would likely be the easiest to convert. Does that seem right? |
![[Next page]](/images/thin_next.gif){kind=small}
![[Go to top]](/images/gototop.gif){kind=small}
top