Oracle functions

Examples of function in oracle 9i,pls check the defination of them if not clear about it.

1.select ascii('A') A,ascii('a') a,ascii(0) zero,ascii(' ') space from dual

65	97	48	32

2.select chr(54740) zhao,chr(65) chr65 from dual

赵	A

3.select concat('0557-','88888888')||'-1234' tel from dual

0557-88888888-1234

4.select initcap('smith') upp from dual

Smith

5.select instr('oracle traning','ra',1,1) instring1,instr('oracle traning','ra',1,2) instring2 from dual

2	9

6.select length('length') from dual

6

7.select lower('ABcCdD') from dual

abccdd

8.select upper('ABcCdD') from dual

ABCCDD

9.select lpad(rpad('gao',10,'*'),17,'&') from dual

&&&&&&&gao*******

10.select rtrim('oracleleleylele','le') from dual

oracleleley

select ltrim('leeleleoracle','le') from dual

oracle

11.select substr('oracle',2,2) from dual

ra

12.select replace('No one love you','No one','I') from dual

I love you

13.select trim('  a bc d d ') from dual

a bc d d

14.select abs(-1) from dual

1

15.select acos(-1) from dual

3.14159265358979

16.select ceil(7.8) from dual

8

17.select exp(1),exp(2) from dual

2.71828182845905	7.38905609893065

18.select floor(7.8) from dual

7

19.select log(2,1),log(2,8) from dual

0       3

20.select mod(2,1),mod(2,8),mod(5,3) from dual

1	0	2	2

21.select power(2,10) from dual

1024

22.select trunc(55.4), trunc(55.5), trunc(-5.4), trunc(-5.5) from dual

55	55	-5	-5

select trunc(123.123,-2),trunc(123.123,2) from dual

100	123.12

select round(55.4), round(55.5), round(-5.4), round(-5.5) from dual

55	56	-5	-6

23.select sign(10),sign(-10),sign(0) from dual

1	-1	0

24.select sqrt(10),sqrt(9),sqrt(0) from dual

3.16227766016838	3	0

25.select to_char(add_months(to_date('200910','yyyymm'),2),'yyyymm') from dual

200912

26.select last_day(sysdate) from dual

2009-12-31 10:27:38

27.select months_between(to_date('19990202','yyyymmdd'),to_date('19990902','yyyymmdd')) from dual

-7

28.select next_day(to_date('20091218','yyyymmdd'),'星期五') from dual

2009-12-25

29.select rowid,rowidtochar(rowid),a.log_content from t_log a

AAAHYnAAJAAAAD4AAA	AAAHYnAAJAAAAD4AAA	haha
AAAHYnAAJAAAAD4AAB	AAAHYnAAJAAAAD4AAB	hahaho

30.select to_multi_byte('gao') from dual

ｇａｏ

31.select to_number('2012') from dual

2012

32.select greatest('2012','2000','2011') from dual

2012

33.select least('2012','2000','2011') from dual

2000

34.select user,uid from dual

JIL	59

35.select userenv('language') from dual

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

36.select (case when 1=2 then 0 else 1 end) from dual

1

37.select nvl(null,2) from dual

2

select nvl(1,2) from dual

1

38.select decode(sign(1+2),-1,2,1,0) from dual

0

select decode(sign(1-2),-1,2,1,0) from dual

2