Hi Friends
Created a Function with a name SPELL_NUMBER to convert Number to Character with Currency . Here I gets problem after decimal point where it create the character with separate number as below its creating as three two Kobo Only where as I want a output as thrity-two kobo
SQL> select spell_number(2423232.32) from dual;
SPELL_NUMBER(2423232.32)
--------------------------------------------------------------------------------
NAIRA two million four hundred twenty-three thousand two hundred thirty-two And
three two Kobo Only
Function
create or replace FUNCTION spell_number(
p_number IN VARCHAR2)
RETURN VARCHAR2
AS
--
type string_array
IS
TABLE OF VARCHAR2(255);
--
v_string string_array := string_array('', ' thousand ', ' million ', ' billion ', ' trillion ', ' quadrillion ', ' quintillion ', ' sextillion ', ' septillion ', ' octillion ', ' nonillion ', ' decillion ', ' undecillion ', ' duodecillion ', ' tridecillion ', ' quaddecillion ', ' quindecillion ', ' sexdecillion ', ' septdecillion ', ' octdecillion ', ' nondecillion ', ' dedecillion ');
v_number VARCHAR2(255);
v_return VARCHAR2(4000);
BEGIN
IF instr(p_number, '.') = 0 THEN
v_number := p_number;
ELSE
v_number := SUBSTR(p_number, 1, instr(p_number, '.')-1);
END IF;
--
IF v_number = '0' OR v_number IS NULL THEN
v_return := 'zero';
ELSE
FOR i IN 1 .. v_string.count
LOOP
EXIT
WHEN v_number IS NULL;
--
IF (SUBSTR(v_number, LENGTH(v_number) -2, 3) <> 0) THEN
v_return := TO_CHAR(to_date(SUBSTR(v_number, LENGTH(v_number)-2, 3), 'j'), 'jsp') || v_string(i) || v_return;
END IF;
v_number := SUBSTR(v_number, 1, LENGTH(v_number)-3);
END LOOP;
END IF;
--
-- to include decimal places.
--
IF p_number LIKE '%.%' THEN
v_number := SUBSTR(p_number, instr(p_number, '.')+1);
--v_return := v_return ||' point';
v_return := 'NAIRA '|| v_return ||' And';
FOR i IN 1 .. LENGTH(v_number)
LOOP
EXIT
WHEN v_number IS NULL;
IF SUBSTR(v_number, 1, 1) = '0' THEN
v_return := v_return ||' zero';
ELSE
v_return := v_return ||' '|| TO_CHAR(to_date(SUBSTR(v_number, 1, 1), 'j'), 'jsp');
END IF;
v_number := SUBSTR(v_number, 2) ;
END LOOP;
END IF; -- include decimal places.
RETURN v_return ||' Kobo Only';
END spell_number;
Sanjay