Home » RDBMS Server » Server Administration » Sql Query
Sql Query [message #373310] |
Wed, 11 April 2001 01:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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?
|
|
|
Re: Sql Query [message #373317 is a reply to message #373310] |
Wed, 11 April 2001 08:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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.
|
|
|
Goto Forum:
Current Time: Fri Sep 06 14:35:57 CDT 2024
|