SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE--------- --------- --------- ---------65 97 48 32
2.CHR给出整数,返回对应的字符;SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C-- -赵 A
3.CONCAT连接两个字符串;SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;
高乾竞电话----------------010-88888888转23
4.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL> select initcap('smith') upp from dual;
UPP-----Smith
5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为1J 出现的位置,默认为1SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING---------9
6.LENGTH返回字符串的长度;SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from .nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))------ ------------ ---------------- ------------ --------- --------------------高乾竞 3 北京市海锭区 6 9999.99 7
7.LOWER返回字符串,并将所有的字符小写SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD--------aabbccdd
8.UPPER返回字符串,并将所有的字符大写SQL> select upper('AaBbCcDd') upper from dual;
UPPER--------AABBCCDD
9.RPAD和LPAD(粘贴字符)RPAD 在列的右边粘贴字符LPAD 在列的左边粘贴字符SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',1-----------------*******gao*******不够字符则用*来填满
10.LTRIM和RTRIMLTRIM 删除左边出现的字符串RTRIM 删除右边出现的字符串SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('-------------gao qian jing
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('HELOVEYOU','HE','I')------------------------------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
