ORACLE SQL 常用函数总结

1、Substr(arg1,arg2,arg3)

说明：arg1是待分割的字符串，arg2和arg3分别是起始分割字符号

     其中arg3可选

例：substr(12345, 1,4) 结果为 1234

补充：两个参数，即arg3为空的用法在SQL SERVER中不适用

 

2、Instr(arg1,arg2,arg3,arg4)

   说明：arg1为待搜索内容，arg2为搜索内容，arg3为搜索起始位置，arg4为搜索匹配位置，即第几次匹配(arg4默认为1)

   例：Instr('CORPORATE FLOOR','OR', 3,2) 结果为 14

 

3、Concat(arg1,arg2)

   说明：将arg1和arg2连接起来，类似于使用||符号进行连接

   例：Concat(1,2) 结果为 12

 

4、Trim(arg)

   说明：默认为去掉arg中的前后空格

   例：Trim(‘ 12345 ‘) 结果为 12345 等同于使用 Trim(‘ ’ from ‘ 12345 ’)

   补充：Trim又可以细分为LTrim和RTrim，分别为去掉左、右空格。

   其他用法：Trim(arg1 from arg2)表示 若arg2以arg1字符开头或结束，则将所有连续的arg1字符去掉，例如 Trim(‘s’ from ‘stuff’) 结果为tuff，Trim(‘f’ from ‘stuff’)结果为 stu

为使意义更清晰，有时还会添加leading和trailing来区分arg2的匹配位置，例如 Trim(‘t’ from ‘test’) 结果为 es

     Trim(leading ‘t’ from ‘test’)结果为 est

     Trim(trailing ‘t’ from ‘test’)结果为 tes

 

5、Length(arg)

   说明：计算arg的长度

   例：Length(12345) 结果为 5

 

6、Ascii(arg)

   说明：返回与指定字符对应的十进制数

   例：Ascii(‘A’) 结果为 65

 

7、Chr(arg)

   说明：返回与指定整数对应的字符

   例：Chr(65) 结果为 A

 

8、Lower(arg)

   说明：将arg全部变为小写

   例：Lower(‘TesT’) 结果为 test

 

9、Upper(arg)

   说明：将arg全部变为大写

   例：Upper(‘TesT’) 结果为 TEST

 

10、Initcap(arg)

说明：将arg的首字母变为大写

例：Initcap(‘test’) 结果为 Test

 

11、LPad(arg1,arg2,arg3) & RPad(arg1,arg2,arg3);

    说明：LPad和RPad分别为在arg1左、右粘贴字符，arg2表示字符串总占据长度，arg3表示的是如果arg1不能占据所有长度，则使用什么进行粘贴。

    例：LPad(‘abc’,5,’*’) 结果为 **abc   RPad(‘abc’,5,’*’) 结果为 abc**

    补充：若arg1的长度大于arg2规定长度，则将arg1按照arg2的长度从第一个字符进行截取，此时arg3将不影响结果，例：LPad(‘abcdefg’,5,’*’)结果为 abcde

 

12、Replace(arg1,arg2,arg3)

    说明：arg1表示待处理的字符串

          arg2表示待替换的字符

          arg3表示要替换成为的字符

    例：Replace('stringtest','t','*') 结果为s*ring*es*

 

13、ABS(arg)

说明：返回arg的绝对值

例：ABS(-100) 结果为 100

    ABS(100) 结果为  100

 

14、Ceil(arg)

说明：返回大于或等于arg数字的最小整数

例：Ceil(3.14) 结果为 4

 

15、Floor(arg)

说明：返回arg数字的整数部分（取整）

例：Floor(3.14) 结果为 3

 

16、Sin(arg)

说明：返回arg数字的正弦值

例：Sin(1.5) 结果为 0.997494986604054

 

17、ASin(arg)

说明：返回反正弦的值

例：ASin(0.5) 结果为 0.523598775598299

 

18、Cos(arg)

说明：返回arg数字的余弦值

例：Cos(-3.1415927) 结果为 -0.999999999999999

 

19、ACos(arg)

说明：返回反余弦的值

例：ACos(-1) 结果为 3.14159265358979

 

20、Tan(arg)

说明：返回arg数字的正切值

例：Tan(20) 结果为 2.23716094422474

 

21、ATan(arg)

说明：返回arg数字的反正切值

例：ATan(1) 结果为 0.785398163397448

 

22、Exp(arg)

说明：返回e的arg次方根

例：Exp(2) 结果为 7.38905609893065

 

23、Ln(arg)

说明：返回arg的对数

例：Ln(1) 结果为 0

 

24、Log(arg1,arg2)

说明：返回以arg1为底arg2的对数

例：Log(2,4) 结果为 2

 

25、Mod(arg1,arg2)

说明：返回arg1除以arg2的余数

例：Mod(3,2) 结果为 1

 

26、Power(arg1,arg2)

说明：返回arg1的arg2次方

例：Power(2,3) 结果为 8

 

27、Sign(arg)

说明：取arg的符号，若arg>0，返回1

                    若 arg=0，返回0

                    若arg<0，返回-1

例：Sign(2), Sign(0), Sign(-2) 结果分别为 1，0，-1

 

28、Sinh(arg)

            说明：返回双曲线的正弦值

            例：Sinh(20) 结果为 242582597.704895

 

29、Cosh(arg)

           说明：返回双曲线的余弦值

            例：Cosh(20) 结果为 242582597.704895

 

30、Sqrt(arg)

            说明：返回数字arg的平方根

            例：sqrt(64) 结果为 8

 

31、Tanh(arg)

            说明：返回双曲线的正切值

            例：Tanh(20) 结果为 1

 

32、Sysdate

            说明：取系统当前时间

            例：SELECT SYSDATE FROM DUAL;

 

33、Greatest

            说明：取一组数中的最大值，比较字符的编码大小

       例：Greatest('A','C','B') 结果为C

 

34、Least

       说明：取一组数中的最小值，比较字符的编码大小

       例：Least('A','C','B') 结果为A

 

35、To_Number

       说明：将字符串转换为数字

       例：To_Number(‘12345’)结果为 12345

       补充：这里的字符串必须由数字组成，否则不能转化

 

36、Last_Day(Sysdate)

       说明：返回该月的最后一天的当前时刻

       例：Last_Day(Sysdate)结果为 2008-10-31 15:28:20

 

37、To_Char(date,’format’)

       说明：将时间转换为指定格式

       例：To_Char(Sysdate,‘yyyy/mm/dd hh24:mi:ss’) 结果为 2008/10/07 15:34:13

 

38、To_Date(string,’format’)

       说明：将string根据给定的时间匹配方式转换成为时间类型

       例：To_Date('20081007153413','yyyymmddhh24miss') 结果为2008-10-7 15:34:13

 

39、Next_Day(date,’dayofweek’)

       说明：计算下个星期相同日的日期

       例：Next_Day(’07-10月-2008’,’星期二’) 结果为 2008-10-14

 

40、Add_Months(date,month)

       说明：给指定日期加上指定月份

       例：To_Char(Add_Months(To_Date('2008,10,0715:52:00',

                                  'yyyy,mm,ddhh24:mi:ss'),

                          1),

              'yyyy,mm,dd hh24:mi:ss')结果为 2008,11,07 15:52:00

 

41、Months_Between(date2,date1)

       说明：返回date2-date1的月份

       例：Months_Between(Add_Months(Sysdate,1),Add_Months(Sysdate,-1)) 结果为 2

 

42、New_Time(date,this,that)

       说明：返回this时区=that时区的日期和时间

       例：SELECT To_Char(Sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,

To_Char(New_Time(Sysdate,'PDT', 'GMT'), 'yyyy.mm.dd hh24:mi:ss') los_angles

          FROM DUAL; 结果为 2008.10.07 16:01:46  2008.10.0723:01:46

 

43、RowIdToChar(rowid)

       说明：将rowid转换成字符

       例：RowIdToChar('AAAAfKAACAAAAEqAAA') 结果为AAAAfKAACAAAAEqAAA

 

44、CharToRowId(char)

       说明：将字符转换成rowid

       例：CharToRowId(‘AAAAfKAACAAAAEqAAA’)结果为 AAAAfKAACAAAAEqAAA

 

45、Convert(c,dset,sset)

       说明：将原字符串c由原字符集sset转到目标字符集dset

       例：Convert('Eleven.Xu 徐','we8hp','f7dec') 结果为 Eleven.Xu e>

 

46、HexToRaw(arg)

       说明：将arg从16进制转换成为raw类型数据

       例：HexToRaw(‘123’) 结果为0123

 

47、RawToHex(arg)

       说明：将arg从raw类型数据转成为16进制数据

       例：RawToHex(‘0123’)结果为 30313233

 

48、To_Multi_Byte(arg)

       说明：将arg从单字节字符转换成为多字节字符

       例：To_Multi_Byte(‘hello’) 结果为ｈｅｌｌｏ

 

49、User

       说明：返回当前用户用户名

       例：SELECT User FROM DUAL 结果为 APPS

 

50、UID

       说明：用户的唯一标识

       例：SELECT du.username,du.user_id

              FROM dba_users du

              WHERE du.username = (SELECT UserFROM DUAL);

 

51、Soundex

       说明：返回一个与给定字符串相同读音的字符串

       例：略

 

52、Userenv(arg)

       说明：若arg为 ‘isdba’，表示判断当前用户是否为DBA

               若arg为 ‘sessionid’，表示判断当前sessionid

若arg为 ‘entryid’，表示会话人口id

若arg为 ‘instance’，表示当前instance标志

若arg为 ‘language’，表示当前环境语言

若arg为 ‘lang’，表示当前环境语言缩写

若arg为 ‘terminal’，表示当前终端或机器的标志

例：Userenv('isdba') 结果为 FALSE

    Userenv('sessionid') 结果为 1416799

              Userenv('entryid') 结果为 0

              Userenv('instance') 结果为 1

              Userenv('language') 结果为SIMPLIFIEDCHINESE_CHINA.UTF8

              Userenv('lang') 结果为 ZHS

              Userenv('terminal') 结果为HAND-ELEVEN

 

53、VSize(arg)

       说明：返回arg的字节数

       例：VSize(‘1234’) 结果为 4

 

54、Decode(value,if1,then1,if2,then2,if3,then3,...,else)

说明：如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。

例：SELECTDecode(Sign(10-11),-1,10,11) FROM DUAL;（取最较小值）结果为 10

补充：详细解释请参考http://blog.csdn.net/elevenXL/archive/2008/10/08/3031647.aspx

 

55、Trunc（arg1,arg2）

       说明：根据arg2规定的精度截取arg1

       例：SELECTTrunc(111.112233,2) FROM DUAL;   结果为 111.11

SELECTTrunc(111.112233,-2) FROM DUAL;  结果为 100 

 

56、Round(arg1,arg2)

       说明：根据arg2规定的精度对arg1按四舍五入进行截取

       例：SELECTRound(111.41,1) FROM DUAL;  结果为 111.4

SELECTRound(111.45,1) FROM DUAL;  结果为 111.5

 

57、BFileName(dir,fullfilename)

       说明：指定外部dir目录下的fullfilename文件，在Insert语句中使用较多

       例：略

 

 

 

附（ORACLESQL 常用函数总结 SQL样例）：

--Oracle SQL 内置函数总结 SQL示例

--Oct.7, 2008 Eleven.Xu Hand,Shanghai

 

--1. Substr

SELECT Substr('12345', 1,4) FROM DUAL;

  --Result:1234

 

--2. Instr

SELECT Instr('CORPORATE FLOOR','OR', 3, 2) FROM DUAL; 

  --Result:14

 

--3. Concat

SELECT Concat(1,2) FROM DUAL; 

  --Result:12

 

--4. Trim

SELECT Trim(' 12345 ') FROM DUAL;   --Result:12345

SELECT LTrim(' 12345 ') FROM DUAL;  --Result:12345

SELECT RTrim(' 12345 ') FROM DUAL;  --Result: 12345

 

SELECT Trim('f' from 'stuff') FROM DUAL;  --Result:stu

SELECT Trim('s' from 'stuff') FROM DUAL;  --Result:tuff

 

SELECT Trim('t' from 'test') FROM DUAL;           --Result:es

SELECT Trim(Leading 't' from 'test') FROM DUAL;   --Result:est

SELECT Trim(Trailing 't' from 'test') FROM DUAL;  --Result:tes

 

--5. Length

SELECT Length(12345) FROM DUAL;

  --Result:5

 

--6. Ascii

SELECT Ascii('A') A,Ascii('a') a,Ascii('0') zero,Ascii(' ') space FROM DUAL;

  --Result:65 97 48 32

 

--7. Chr

SELECT Chr(65) FROM DUAL;

  --Result:A

 

--8. Lower

SELECT Lower('TesT') FROM DUAL;

  --Result:test

 

--9. Upper

SELECT Upper('TesT') FROM DUAL;

  --Result:TEST

 

--10. Initcap

SELECT Initcap('test') FROM DUAL;

  --Result:Test

 

--11. LPad&RPad

SELECT LPad('abc',5,'*') FROM DUAL;      --Result:**abc

SELECT RPad('abc',5,'*') FROM DUAL;      --Result:abc**

SELECT LPad('abcdefg',5,'*') FROM DUAL;  --Result:abcde

 

--12. Replace

SELECT Replace('string test','t','*') FROM DUAL; 

  --Result:s*ring*es*

 

--13. ABS

SELECT ABS(-100) FROM DUAL;  --Result:100

SELECT ABS(100) FROM DUAL;   --Result:100

 

--14. Ceil

SELECT Ceil(3.14) FROM DUAL;

  --Result:4

 

--15. Floor

SELECT Floor(3.14) FROM DUAL;

  --Result:3

 

--16. Sin

SELECT Sin(1.5) FROM DUAL;

  --Result:0.997494986604054

 

--17. ASin

SELECT ASin(0.5) FROM DUAL;

  --Result:0.523598775598299

 

--18. Cos

SELECT Cos(-3.1415927) FROM DUAL;

   --Result:-0.999999999999999

  

--19. ACos

SELECT ACos(-1) FROM DUAL;

  --Result:3.14159265358979

 

--20. Tan

SELECT Tan(20) FROM DUAL;

  --Result:2.23716094422474

 

--21. ATan

SELECT ATan(1) FROM DUAL;

  --Result:0.785398163397448

 

--22. Exp

SELECT Exp(2) FROM DUAL;

  --Result:7.38905609893065

 

--23. Ln

SELECT Ln(1) FROM DUAL;

  --Result:0

 

--24. Log

SELECT Log(2,4) FROM DUAL;

  --Result:2

 

--25. Mod

SELECT Mod(3,2) FROM DUAL;

  --Result:1

 

--26. Power

SELECT Power(2,3) FROM DUAL;

  --Result:8

 

--27. Sign

SELECT Sign(2), Sign(0), Sign(-2) FROM DUAL;

  --Result:1 0 -1

 

--28. Sinh

SELECT Sinh(20) FROM DUAL;

  --Result:242582597.704895

 

--29. Cosh

SELECT Cosh(20) FROM DUAL;

  --Result:242582597.704895s

 

--30. Sqrt

SELECT Sqrt(64) FROM DUAL;

  --Result:8

 

--31. Tanh

SELECT Tanh(20) FROM DUAL;

  --Result:1

 

--32. Sysdate

SELECT Sysdate FROM DUAL;

  --Result:2008-10-7 14:13:53

 

--33. Greatest

SELECTGreatest(1,2,3) FROM DUAL;        --Result:3

SELECTGreatest('A','C','B') FROM DUAL;  --Result:C

 

--34. Least

SELECT Least(1,2,3) FROM DUAL;        --Result:1

SELECT Least('A','C','B') FROM DUAL;  --Result:A

 

--35. To_Number

SELECTTo_Number('12345') FROM DUAL;

  --Result:12345

 

--36. Last_Day

SELECTLast_Day(Sysdate) FROM DUAL;

  --Result:2008-10-31 15:28:20

 

--37. To_Char

SELECT To_Char(Sysdate, 'yyyy/mm/ddhh24:mi:ss') FROM DUAL;

  --Result:2008/10/07 15:34:13

 

--38. To_Date

SELECT To_Date('20081007153413','yyyymmddhh24miss') FROM DUAL;

  --Result:2008-10-7 15:34:13

 

--39. Next_Day

SELECTNext_Day('07-10月-2008','星期二') FROM DUAL;

  --Result:2008-10-14

 

--40. Add_Months

SELECTAdd_Months(sysdate,2) FROM DUAL;  --Result:2008-12-7 15:52:00

SELECTTo_Char(Add_Months(To_Date('2008,10,07 15:52:00',

                                  'yyyy,mm,ddhh24:mi:ss'),

                          1),

               'yyyy,mm,ddhh24:mi:ss')

  FROM DUAL;                             --Result:2008,11,07 15:52:00

 

--41. Months_Between

SELECTMonths_Between(Add_Months(Sysdate,1),Add_Months(Sysdate,-1)) FROM DUAL;

  --Result:2

 

--42. New_Time

SELECT To_Char(Sysdate, 'yyyy.mm.ddhh24:mi:ss')bj_time,

       To_Char(New_Time(Sysdate, 'PDT', 'GMT'), 'yyyy.mm.ddhh24:mi:ss')los_angles

  FROM DUAL;

  --Result:2008.10.07 16:01:46  2008.10.07 23:01:46

 

--43. RowIdToChar

SELECTRowIdToChar('AAAAfKAACAAAAEqAAA') FROM DUAL;

  --Result:AAAAfKAACAAAAEqAAA

 

--44. CharToRowId

SELECTCharToRowId('AAAAfKAACAAAAEqAAA') FROM DUAL;

  --Result:AAAAfKAACAAAAEqAAA

 

--45. Convert

SELECT Convert('Eleven.Xu 徐','we8hp','f7dec')"conversion" FROM DUAL;

  --Result:Eleven.Xu e>_

 

--46. HexToRaw

SELECTHexToRaw('123') FROM DUAL;

  --Result:0123

 

--47. RawToHex

SELECTRawToHex('0123') FROM DUAL;

  --Result:30313233

 

--48. To_Multi_Byte

SELECTTo_Multi_Byte('hello') FROM DUAL;

 

--49. User

SELECT User FROM DUAL;

  --Result:APPS

 

--50. UID

SELECTdu.username, du.user_id

  FROM dba_users du

 WHERE du.username = (SELECT User FROM DUAL);

  --Result:APPS 47

 

--51. Soundex

--略

 

--52. Userenv

SELECT Userenv('isdba') FROM DUAL;      --Result:FALSE

SELECT Userenv('sessionid') FROM DUAL;  --Result:1416799

SELECT Userenv('entryid') FROM DUAL;    --Result:0

SELECT Userenv('instance') FROM DUAL;   --Result:1

SELECT Userenv('language') FROM DUAL;   --Result:SIMPLIFIED CHINESE_CHINA.UTF8

SELECT Userenv('lang') FROM DUAL;       --Result:ZHS

SELECT Userenv('terminal') FROM DUAL;   --Result:HAND-ELEVEN

 

--53. VSize

SELECT VSize('1234') FROM DUAL; 

  --Result:4

 

--54. Decode

SELECTDecode(Sign(10-11),-1,10,11) FROM DUAL; 

  --Result:10

 

--55. Trunc

SELECT Trunc(111.112233,2) FROM DUAL;   --Result:111.11

SELECT Trunc(111.112233,-2) FROM DUAL;  --Result:100 

 

--56. Round

SELECT Round(111.41,1) FROM DUAL;  --Result:111.4

SELECT Round(111.45,1) FROM DUAL;  --Result:111.5

 

--57. BFileName

--略

 

注：1、以上SQL脚本均测试通过

2、以上内容均为本人整理、收集，转帖请注明出处，如需要可发邮件索取电子版和SQL脚本（[e-mail]）