11.SUBSTR(string,start,count)取子字符串,从start开始,取count个SQL> select substr('13088888888',3,8) from dual;
SUBSTR('--------08888888
12.REPLACE('string','s1','s2')string 希望被替换的字符或变量 s1 被替换的字符串s2 要替换的字符串SQL> select replace('he love you','he','i') from dual;
REPLACE('H----------i love you
13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQL> create table table1(xm varchar(8));SQL> insert into table1 values('weather');SQL> insert into table1 values('wether');SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM--------weatherwether
14.TRIM('s' from 'string')LEADING 剪掉前面的字符TRAILING 剪掉后面的字符如果不指定,默认为空格符
15.ABS返回指定值的绝对值SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)--------- --------- 100 100
16.ACOS给出反余弦的值SQL> select acos(-1) from dual;
ACOS(-1)---------3.1415927
17.ASIN给出反正弦的值SQL> select asin(0.5) from dual;
ASIN(0.5)---------.52359878
18.ATAN返回一个数字的反正切值SQL> select atan(1) from dual;
ATAN(1)---------.78539816
19.CEIL返回大于或等于给出数字的最小整数SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)--------------- 4
20.COS返回一个给定数字的余弦SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)--------------- -1
21.COSH返回一个数字反余弦值SQL> select cosh(20) from dual;
COSH(20)---------242582598
22.EXP返回一个数字e的n次方根SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)--------- ---------7.3890561 2.7182818
23.FLOOR对给定的数字取整数SQL> select floor(2345.67) from dual;
FLOOR(2345.67)-------------- 2345
24.LN返回一个数字的对数值SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)--------- --------- ------------- 0 .69314718 .99999999
25.LOG(n1,n2)返回一个以n1为底n2的对数 SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)--------- --------- 0 2
26.MOD(n1,n2)返回一个n1除以n2的余数SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)--------- --------- --------- 1 0 2
27.POWER返回n1的n2次方根SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)----------- ---------- 1024 27
28.ROUND和TRUNC按照指定的精度进行舍入SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)----------- ------------ ----------- ------------ 56 -55 55 -55
29.SIGN取数字n的符号,大于0返回1,小于0返回-1,等于0返回0SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)--------- ---------- --------- 1 -1 0
30.SIN返回一个数字的正弦值SQL> select sin(1.57079) from dual;
SIN(1.57079)------------ 1