OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Sql Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql Query [message #373310]

Wed, 11 April 2001 01:19

yram
Messages: 75
Registered: February 2001

Member

I want to convert 34 into Thirty Four ,i want a query that will be good for 'N' number of digits?

Report message to a moderator

Re: Sql Query [message #373317 is a reply to message #373310]

Wed, 11 April 2001 08:00

Bala
Messages: 205
Registered: November 1999

Senior Member

Hi,

I got this script from some where ......

create or replace
FUNCTION In_Words(p_Number NUMBER
,p_Sign VARCHAR2 := 'SIGN')
RETURN VARCHAR2
IS
v_Space INTEGER;
v_Sign VARCHAR2(6);
v_String VARCHAR2(64);
v_Thousands VARCHAR2(64);
v_Millions VARCHAR2(64);
v_Hmillions VARCHAR2(64);
BEGIN
IF Length(v_String) > 12 THEN
RETURN 'Number entered must be between +999999999999 or -999999999999';
END IF;

IF p_Number < 0 and upper(p_Sign) = 'SIGN' THEN
v_Sign := 'Minus ';
END IF;

v_String := to_char(abs(p_Number));

IF Length(v_String) > 11 THEN
v_Hmillions := replace(In_Words(substr(v_String,1,length(v_String)-11),'NOSIGN')
,' And',' and')||' Hundred and ';
v_String := substr(v_String,length(v_String)-10);
END IF;

IF length(v_String) > 6 THEN
v_Millions := replace(In_Words(substr(v_String,1,length(v_String)-6),'NOSIGN')
,' And',' and')||' Million ';
v_String := substr(v_String,length(v_String)-5);
END IF;

IF length(v_String) = 5 THEN
v_Thousands := replace(In_Words(substr(v_String,1,2),'NOSIGN')
,' And',' and')||' Thousand ';
v_String := substr(v_String,3);
ELSIF length(v_String) = 6 THEN
v_Thousands := replace(In_Words(substr(v_String,1,3),'NOSIGN')
,' And',' and')||' Thousand ';
v_String := substr(v_String,4);
END IF;

IF to_number(v_String) = 0 THEN
v_String := '';
ELSE
v_String := initcap(replace(to_char(to_date(v_String,'YYYY'),'YYYYsp'),'-','ZZ'));
IF abs(p_Number) > 100 AND mod(abs(p_Number),100) <> 0 THEN
v_Space := instr(v_String,' ',-1);
v_string := substr(v_String,1,v_Space)||' and '||substr(v_String,v_Space);
END IF;
END IF;
RETURN replace(v_Sign||v_Hmillions||v_Millions||v_Thousands||
replace(v_String,'zz','-'),' ',' ');
END;
/

call this function in you query like

SQL> select in_words(34) from dual

IN_WORDS(34)
-----------------
Thirty-four

Bala.

Report message to a moderator

Previous Topic:	Shutdown immediate
Next Topic:	Performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 00:45:34 CDT 2024