plsql convert number to text

CREATE OR REPLACE FUNCTION APPS.Xxxt_Yaziyacevir_Tr(p_num IN NUMBER, p_currency_code IN VARCHAR2 DEFAULT 'TRL')

RETURN VARCHAR2 AUTHID CURRENT_USER IS

----------------------------------------------------------------------------------------------------------------------

FUNCTION readn(c IN VARCHAR2,g IN NUMBER,n IN NUMBER) RETURN VARCHAR2 IS

p_text VARCHAR2(50);

BEGIN

IF n=0 THEN

RETURN NULL;

END IF;

SELECT txt

INTO p_text

FROM xxtc_num2text

WHERE currency_code = c

AND grp = g

AND num = n;

RETURN (p_text);

EXCEPTION WHEN NO_DATA_FOUND THEN NULL;

END;

----------------------------------------------------------------------------------------------------------------------

BEGIN

DECLARE

x INTEGER;

b INTEGER;

grp INTEGER;

txt VARCHAR2(100);

inturn VARCHAR2(100);

n VARCHAR2(100);

pp_num VARCHAR2(30);

pp_currency_code VARCHAR2(3) := 'TRK';

pp_str VARCHAR2(800);

nokta_yer NUMBER;

pp_ondalik VARCHAR(10);

pp_kurus_txt VARCHAR2(100);

BEGIN

n := '000';

pp_num := TO_CHAR(TRUNC(p_num));

nokta_yer := INSTR(TO_CHAR(ROUND(p_num,2)),'.');

IF nokta_yer > 0 THEN

pp_ondalik := SUBSTR(TO_CHAR(ROUND(p_num,2)),nokta_yer+1,2);

ELSIF nokta_yer = 0 THEN

pp_ondalik := NULL;

END IF;

IF LENGTH(pp_ondalik) = 1 THEN

pp_ondalik := pp_ondalik'0';

END IF;

/*

IF p_currency_code in ('TRL','TRK','TRY','TR','YTR','YTL') THEN

pp_currency_code := 'TRK';

ELSIF p_currency_code in ('USD','AMD', 'EUR', 'GBP') THEN

pp_currency_code := 'USD';

END IF;

*/

x := LENGTH(pp_num);

b := x MOD 3;

grp := (x-b)/3+1;

IF b = 0 THEN

b := 3;

grp := grp-1;

END IF;

pp_num := SUBSTR(n,1,3-b)pp_num;

b := 1;

pp_str := ' ';

WHILE grp > 0 LOOP

inturn := SUBSTR(pp_num,b,3);

IF TO_NUMBER(inturn) > 0 THEN

IF SUBSTR(inturn,1,1) > '1' THEN

pp_str:=pp_strreadn(pp_currency_code,1,SUBSTR(inturn,1,1));

END IF;

IF SUBSTR(inturn,1,1) > '0' THEN

pp_str:=pp_strreadn(pp_currency_code,3,1);

END IF;

n := SUBSTR(inturn,2,2);

IF TO_NUMBER(n) >= 20 THEN

pp_str:=pp_strreadn(pp_currency_code,2,TO_NUMBER(SUBSTR(n,1,1)));

pp_str:=pp_strreadn(pp_currency_code,1,TO_NUMBER(SUBSTR(n,2,1)));

ELSE

pp_str:=pp_strreadn(pp_currency_code,1,TO_NUMBER(SUBSTR(n,1,2)));

END IF;

IF grp > 1 THEN

pp_str:=pp_strreadn(pp_currency_code,3,grp);

END IF;

IF LTRIM(RTRIM(UPPER(pp_str))) = 'BÝRBÝN' THEN

pp_str := 'BIN';

END IF;

END IF;

b := b+3;

grp := grp-1;

END LOOP;

-- kurus kismi

IF pp_ondalik IS NOT NULL THEN

IF TO_NUMBER(pp_ondalik) < 20 AND TO_NUMBER(pp_ondalik) != 10 THEN

pp_kurus_txt := readn(pp_currency_code,1,TO_NUMBER(pp_ondalik));

ELSE

pp_kurus_txt := readn(pp_currency_code,2,TO_NUMBER(SUBSTR(pp_ondalik,1,1)));

IF SUBSTR(pp_ondalik,2,1) IS NOT NULL THEN

pp_kurus_txt := pp_kurus_txt readn(pp_currency_code,1,TO_NUMBER(SUBSTR(pp_ondalik,2,1)));

END IF;

END IF;

END IF; -- IF pp_ondalik is not null then

IF pp_str = ' ' THEN

pp_str := '';

END IF;

IF NVL(pp_kurus_txt,0) = 0 THEN

IF p_currency_code IN ('TRL','TRK','TRY','TR','YTR','YTL') THEN

RETURN('YALNIZ #'NVL(pp_str,' SIFIR ')'# Yeni TL. # ' );

ELSIF p_currency_code = 'USD' THEN

RETURN('YALNIZ #'NVL(pp_str,' ZERO ')'# USD # ');

ELSIF p_currency_code = 'EUR' THEN

RETURN('YALNIZ #'NVL(pp_str,' ZERO ')'# EUR # ');

ELSIF p_currency_code = 'GBP' THEN

RETURN('YALNIZ #'NVL(pp_str,' ZERO ')'# GBP # ');

END IF;

ELSE

IF p_currency_code IN ('TRL','TRK','TRY','TR','YTR','YTL') THEN

RETURN('YALNIZ #'NVL(pp_str,' SIFIR ')'# Yeni TL. # ' NVL(pp_kurus_txt,'SIFIR') '# Yeni KRS. dur');

ELSIF p_currency_code = 'USD' THEN

RETURN('YALNIZ #'NVL(pp_str,' ZERO ')'# USD # ' NVL(pp_kurus_txt,'SIFIR') '# CENT tir');

ELSIF p_currency_code = 'EUR' THEN

RETURN('YALNIZ #'NVL(pp_str,' ZERO ')'# EUR # ' NVL(pp_kurus_txt,'SIFIR') '# CENT tir');

ELSIF p_currency_code = 'GBP' THEN

RETURN('YALNIZ #'NVL(pp_str,' ZERO ')'# GBP # ' NVL(pp_kurus_txt,'SIFIR') '# PENNY dir');

END IF;

END IF;

END;

END;

/

select to_char(to_date('222','j'),'jsp') from dual

The "j" is for Julian Date, actually the numbers of days since 12/31/4713 BCand the "sp" is telling the output to be spelled out such as "nine hundred"So the 1st "j" converts 900 to the julian date of 900 days since 4713 BC andis stored in memory as 900 Julian. The to_char then takes the julian dateandconverts it to 900 julian character "j" and spells the 900 out to ninehundred "sp".