Tuesday 13 March 2012

This query converts numbers into their text equivalents

select to_char(to_date(873,'J'), 'JSP') as converted_form from dual;

CONVERTED_FORM
---------------------------
EIGHT HUNDRED SEVENTY-THREE

J represents the Julian format. When the to_char function is applied to the result of to_date, it spells (SP) the word for the number passed to to_date. 

or


create or replace function number_to_word(vin_number in number)
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );
cur_number varchar2(50) default trunc(vin_number);
vout_word varchar2(4000);
begin
vout_word := to_char(to_date(substr(cur_number, length(cur_number)-2, 3),'j' ),'jspth' ) || l_str(1) || vout_word;
cur_number := substr( cur_number, 1, length(cur_number)-3 );

for i in 2 .. l_str.count loop
exit when cur_number is null;

if ( substr(cur_number, length(cur_number)-2, 3) <> 0 ) then
vout_word := to_char(to_date(substr(cur_number, length(cur_number)-2, 3),'j' ),'jsp' ) || l_str(i) || vout_word;
end if;
cur_number := substr( cur_number, 1, length(cur_number)-3 );
end loop;
return initcap(nvl(vout_word, 'zero'));
end;
/
or

select to_char(to_date(121,'J'), 'JSP') as converted_form from dual;

or

SELECT (CASE WHEN SAL=0 THEN 'ZERO Only' else spell_money(AMOUNT)
end)amount_in_word
FROM salary_master

or

CREATE OR REPLACE
FUNCTION To_Words(NumberIN IN NUMBER,
FormatIN IN VARCHAR2 DEFAULT 'C$D.D',
ScaleIN IN NUMBER DEFAULT NULL
) RETURN VARCHAR2
IS
InvalidNumberFormatModel EXCEPTION;
PRAGMA EXCEPTION_INIT(InvalidNumberFormatModel,-1481);
InvalidNumber EXCEPTION;
PRAGMA EXCEPTION_INIT(InvalidNumber,-1722);
TYPE GroupTableType IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
Position PLS_INTEGER := 1;
ConversionCase CHAR(1) := 'L';
ConversionType CHAR(1) := '$';
WholeZero CHAR(1) := 'D';
FractionalZero CHAR(1) := 'D';
GroupTable GroupTableType;
GroupIndex NUMBER;
Filler CHAR(1);
Words VARCHAR2(2000);
WholePart NUMBER;
FractionalPart NUMBER;
FractionalDigits NUMBER;
Remainder NUMBER;
Suffix VARCHAR2(50);
BEGIN
-- Validate format
Filler := UPPER(SUBSTR(FormatIN,Position,1));
IF Filler IN ('U','L','C')
THEN
ConversionCase := Filler;
Position := Position + 1;
Filler := UPPER(SUBSTR(FormatIN,Position,1));
END IF;
IF Filler IN ('N','$')
THEN
IF Filler = 'N'
THEN
ConversionType := 'N';
WholeZero := 'S';
FractionalZero := 'S';
END IF;
Position := Position + 1;
Filler := UPPER(SUBSTR(FormatIN,Position,1));
END IF;
IF Filler IN ('S','D')
THEN
WholeZero := Filler;
Position := Position + 1;
Filler := UPPER(SUBSTR(FormatIN,Position,1));
END IF;
IF Filler = '.'
THEN
Position := Position + 1;
Filler := UPPER(SUBSTR(FormatIN,Position,1));
IF Filler IN ('S','D')
THEN
FractionalZero := Filler;
Position := Position + 1;
Filler := UPPER(SUBSTR(FormatIN,Position,1));
END IF;
END IF ;
IF Filler IS NOT NULL
THEN
RAISE InvalidNumberFormatModel;
END IF;
IF NumberIN IS NULL
THEN
RETURN NULL;
END IF;
-- Initialize Group Table
GroupTable(0) := '';
GroupTable(1) := ' ten';
GroupTable(2) := ' hundred';
GroupTable(3) := ' thousand';
GroupTable(4) := ' ten thousand';
GroupTable(5) := ' hundred thousand';
GroupTable(6) := ' million';
GroupTable(7) := ' ten million';
GroupTable(8) := ' hundred million';
GroupTable(9) := ' billion';
GroupTable(10) := ' ten billion';
GroupTable(11) := ' hundred billion';
GroupTable(12) := ' trillion';
GroupTable(13) := ' ten trillion';
GroupTable(14) := ' hundred trillion';
GroupTable(15) := ' quadrillion';
GroupTable(16) := ' ten quadrillion';
GroupTable(17) := ' hundred quadrillion';
GroupTable(18) := ' quintillion';
GroupTable(19) := ' ten quintillion';
GroupTable(20) := ' hundred quintillion';
GroupTable(21) := ' sextillion';
GroupTable(22) := ' ten sextillion';
GroupTable(23) := ' hundred sextillion';
GroupTable(24) := ' septillion';
GroupTable(25) := ' ten septillion';
GroupTable(26) := ' hundred septillion';
GroupTable(27) := ' octillion';
GroupTable(28) := ' ten octillion';
GroupTable(29) := ' hundred octillion';
GroupTable(30) := ' nonillion';
GroupTable(31) := ' ten nonillion';
GroupTable(32) := ' hundred nonillion';
GroupTable(33) := ' nonillion';
GroupTable(34) := ' ten nonillion';
GroupTable(35) := ' hundred nonillion';
GroupTable(36) := ' decillion';
GroupTable(37) := ' ten decillion';
GroupTable(38) := ' hundred decillion';
-- Calculate whole and fractional parts
WholePart := ABS(TRUNC(NumberIN));
FractionalPart := ABS(NumberIN) - WholePart;
-- Check if fractional part is 0
IF FractionalPart = 0
THEN
-- When fractional part is 0.
-- Check if 0 fractional part should be displayed.
IF FractionalZero = 'D'
THEN
IF ConversionType = '$'
THEN
Words := 'zero cents';
ELSE
Words := 'zero tenth';
END IF;
Suffix := ' and ';
END IF;
ELSE
-- When fractional part is not 0.
-- Calculate fractional part digit number based on conversion
-- type. Note, TO_CHAR results in an additional character for
-- the decimal point. Therefore, we subtract 1 from LENGTH.
IF ConversionType = 'N'
THEN
FractionalDigits := LENGTH(TO_CHAR(FractionalPart)) - 1;
IF FractionalDigits > 38
THEN
RAISE InvalidNumber;
END IF;
Suffix := GroupTable(FractionalDigits) || 'th';
FractionalPart := FractionalPart *
POWER(10,FractionalDigits);
ELSE
-- Dollar amount should not have more than two fractional
-- digits. TO_CHAR results in an additional character for
-- the decimal point. Therefore, we compare LENGTH to 3.
IF LENGTH(TO_CHAR(FractionalPart)) > 3
THEN
RAISE InvalidNumber;
END IF;
FractionalDigits := 2;
FractionalPart := FractionalPart * 100;
IF FractionalPart = 1
THEN
Suffix := ' cent';
ELSE
Suffix := ' cents';
END IF;
END IF;
IF FractionalPart <= 5373484
THEN
Words := TO_CHAR(TO_DATE(FractionalPart,'j'),'jsp') ||
Suffix;
ELSE
GroupIndex := 0;
Filler := NULL;
WHILE FractionalPart != 0 LOOP
Remainder := Mod(FractionalPart,1000);
IF Remainder != 0
THEN
Words := TO_CHAR(TO_DATE(Remainder,'j'),'jsp') ||
GroupTable(GroupIndex) || Filler ||
Words;
Filler := ' ';
END IF;
GroupIndex := GroupIndex + 3;
FractionalPart:= TRUNC(FractionalPart / 1000);
END LOOP;
Words := Words || Suffix;
END IF;
Suffix := ' and ';
END IF;
-- Check if Whole Part is 0
IF WholePart = 0
THEN
-- When whole part is 0.
-- Check if 0 whole part should be displayed.
IF WholeZero = 'D'
THEN
IF ConversionType = '$'
THEN
Words := 'zero dollars' || Suffix || Words;
ELSE
Words := 'zero' || Suffix || Words;
END IF;
END IF;
ELSE
-- When whole part is not 0.
-- Check if dollar conversion.
IF ConversionType = '$'
THEN
-- When dollar conversion.
-- Check if whole part is equal to 1.
IF WholePart = 1
THEN
Suffix := ' dollar' || Suffix;
ELSE
Suffix := ' dollars' || Suffix;
END IF;
END IF;
IF WholePart <= 5373484
THEN
Words := TO_CHAR(TO_DATE(WholePart,'j'),'jsp') ||
Suffix || Words;
ELSE
IF LENGTH(TO_CHAR(WholePart)) > 38
THEN
RAISE InvalidNumber;
END IF;
GroupIndex := 0;
Filler := NULL;
Words := Suffix || Words;
WHILE WholePart != 0 LOOP
Remainder := Mod(WholePart,1000);
IF Remainder != 0
THEN
Words := TO_CHAR(TO_DATE(Remainder,'j'),'jsp') ||
GroupTable(GroupIndex) || Filler ||
Words;
Filler := ' ';
END IF;
GroupIndex := GroupIndex + 3;
WholePart := TRUNC(WholePart / 1000);
END LOOP;
END IF;
END IF;
IF Words IS NULL
THEN
Words := 'zero';
END IF;
IF Sign(NumberIN) = -1
THEN
Words := 'minus ' || Words;
END IF;
-- Convert to proper case.
IF ConversionCase = 'U'
THEN
Words := UPPER(Words);
ELSIF ConversionCase = 'C'
THEN
Words := INITCAP(Words);
END IF;
RETURN Words;
END;

or

SELECT ename,sal,TO_CHAR(TO_DATE(sal,'J'),'JSP')||' ONLY/-' AMOUNT_WORDS
FROM emp
------this Will give u the following Result:--

ENAME SAL AMOUNT_WORDS

or

function to_words(amt number, curr varchar2 default
'IRS.', denom varchar2 default 'Paisa')
return varchar2 is
amt_in_words varchar2(500);
begin
select
decode(amt, null, null,
curr||
initcap(
decode(floor(floor(AMT)/1e9), 0, null,

to_char(to_date(to_char(floor(floor(AMT)/1e9)), 'J'),
' Jsp ')||'Billion')||
decode(floor(mod(floor(AMT), 1e9)/1e6), 0,
null,

to_char(to_date(to_char(floor(mod(floor(AMT),
1e9)/1e6)),
'J'), ' Jsp ')||'Million')||
decode(floor(mod(floor(AMT), 1e6)/1000), 0,
null,

to_char(to_date(to_char(floor(mod(floor(AMT),
1e6)/1000)),
'J'), ' Jsp ')||'Thousand')||
decode(mod(floor(AMT), 1000), 0, null,
to_char(to_date(to_char(mod(floor(AMT),
1000)), 'J'), ' Jsp'))||
decode(round(mod(AMT*100, 100)), 0, ' Only',
' and '||denom||' '||
to_char(to_date(to_char(round(mod(AMT*100,
100))),'J'), 'Jsp')||
' Only')
)
)
into amt_in_words
from dual;
return(amt_in_words);
end;


FUNCTION spell_amt(in_val IN number) RETURN varchar2 IS
billion number(15);
million number(12);
thousand number(9);
paisas number(6);
spell_amt varchar2(160);
begin
select to_number(substr(lpad((in_val - mod(in_val,1)),15,'0'),-12,3)),
to_number(substr(lpad((in_val - mod(in_val,1)),15,'0'),-9,3)),
to_number(substr(lpad((in_val - mod(in_val,1)),15,'0'),-6,6)),
to_number(rpad(substr(mod(in_val,1),2,2),2,'0'))
into
billion,million,thousand,paisas from dual;
if nvl(billion,0) != 0 then
select initcap(to_char(to_date(billion,'j'),'jsp')) || ' Billion'
into
spell_amt from dual;
end if;
if nvl(million,0) != 0 then
select rtrim(spell_amt) || ' ' ||
initcap(to_char(to_date(million,'j'),'jsp')) || ' Million'
into
spell_amt from dual;
end if;

if nvl(thousand,0) != 0 then
select rtrim(spell_amt) || ' ' ||
initcap(to_char(to_date(thousand,'j'),'jsp'))
into
spell_amt from dual;
end if;
if nvl(paisas,0) != 0 then
select rtrim(spell_amt) || ' And ' ||
initcap(to_char(to_date(paisas,'j'),'jsp')) || ' Paisas' ||' Only.'
into
spell_amt from dual;
end if;
RETURN SPELL_AMT;
END SPELL_AMT;



No comments:

Post a Comment