Convert a number to word

select to_char(to_date('1983','J'),'JSP') from dual;

In this query the number is converting into Julian date then by using fromat  'SP' to spell out

Example

  select to_char(sysdate,'DDSP'),to_char(sysdate,'MMSP'),to_char(sysdate,'YYYYSP') from dual  -- Spells the day ,month and the year

 In this only value upto 5373484 can be spelled  ; giving greater value will result in the following error

 ORA-01854: Julian date must be between 1 and 5373484

 select to_char(to_date('190000083','J'),'JSP') from dual;

 ORA-01830: date format picture ends before converting entire input string


Query to Spell  Rupee and Paise

select
TO_CHAR(TO_DATE(substr(10000.123,1,instr(10000.123,'.')-1),'J'),'JSP')||' RUPEES AND  '||
replace(replace(replace(replace(TO_CHAR(TO_DATE(substr(10000.123,instr(10000.123,'.')+1,length(10000.123)),'J'),'JSP'),'MILLION',''),'HUNDRED',''),'THOUSAND',''),'-',' ')  ||' PAISE ONLY' as number_char
from dual;

----------

Query to convert NUMBER to words

In Oracle 9i


SELECT  LEVEL+1 asnumber FROM dual WHERE to_char(to_date(LEVEL+1,'J'), 'JSP')  = 'ONE THOUSAND'
CONNECT BY to_char(to_date(LEVEL,'J'), 'JSP')  != 'ONE THOUSAND';

OR

SELECT  LEVEL FROM dual WHERE to_char(to_date(LEVEL,'J'), 'JSP')  = 'ONE THOUSAND ONE HUNDRED ELEVEN'
CONNECT BY to_char(to_date(LEVEL-1,'J'), 'JSP')  != 'ONE THOUSAND ONE HUNDRED ELEVEN'
AND LEVEL < 10001  -- Your get out of jail clause!;


In Oracle 10g

select sp, n from (select 'FIVE THOUSAND ONE' sp from dual)
   model dimension by (1 dim) measures (0 n, sp)  rules iterate (10000) until (to_char(date '2000-01-01' +
(ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])  ( n[1]=ITERATION_NUMBER);


-------

Alphabets,Level, Analytic Query

Alphabets

WITH AlphaBet AS
( SELECT CHR((ROWNUM + 64)) AS Character
FROM DUAL CONNECT BY level <= 157)
SELECT * FROM AlphaBet;

------------------------------------------------------------------------
Some Analytic Functions

SELECT G.*,ROW_NUMBER() OVER(PARTITION BY G.DEPTNO ORDER BY G.EMPNO)
FROM EMP G;

SELECT G.DEPTNO,G.ENAME,SUM(G.SAL) OVER (PARTITION BY G.DEPTNO ORDER BY G.DEPTNO) FROM EMP G ;

SELECT FIRST_VALUE(G.EMPNO) OVER() FROM EMP G;

---------------------------------------------------------------------------
Level Query


select level,lpad(' ',3*(level)) || VC_designation s,nn_desgcode dsg,nn_repdesgcode rcd,
SYS_CONNECT_BY_path (nn_repdesgcode,'/') op,
substr(SYS_CONNECT_BY_path (nn_repdesgcode,'/'),3,
length((SYS_CONNECT_BY_path (nn_repdesgcode,'/')))) hjdd
from hr_designation_mast where nn_repdesgcode is not null
start with nn_repdesgcode is not null
connect by prior nn_desgcode=nn_repdesgcode