2 字符函数
1. replace( 字符串1,字符串2,字符串3)
replace( char, search_string, replace_string)
功能：在“字符串1”中搜索“字符串2”，并将其替换为“字符串3”。
例如下面的命令是将所有员工名字中出现的”a”替换为”中国”。
sql>selectreplace(ename, ‘a’, ‘中国’) from scott.emp;
2. instr(c1, c2, i, j)
功能：在一个字符串中搜索指定的字符，返回发现指定的字符的位置。其中：
c1被搜索的字符串
c2希望搜索的字符串
i 搜索开始位置，默认为1
j 第j次出现，默认为1
例如下面的命令是找出”oracletraning” 第二个ra出现的位置。
sql>selectinstr(‘oracle traing’ , ‘ra’,1,2) from dual;
3. ascii(单个字符)
功能：返回与指定字符对应的十进制数。
sql>selectascii (‘a’) a, ascii(‘a’) a , ascii (‘ ‘) space from dual;
说明：dual是oracle内部提供的一个用于临时数据计算的特殊表，它只有一列dummy。
4. chr(整数)
功能：给出整数，返回对应的字符。
sql>selectchr(54740) zhao, chr(65) char65 from dual;
5. concat(字符串1,字符串2)
功能：连接两个字符串。
selectconcat(‘0532-‘, ‘96656’) || ‘拨 0’ 崂山矿泉订水 fromdual;
selectconcat (ename, ‘是优秀员工’) from scott.emp;
该函数和|| 的作用是一样的。
6. initcap（字符串）
功能：返回字符串并将字符串的第一个字母变为大写。
selectinitcap(‘smith’) upp from dual;
selectinitcap(ename) ename from scott.emp;
7. length(字符串)
功能：返回字符串的长度
例如：查询雇员姓名，姓名字符长度，工资及工资数字长度。
selectename, length(ename), sal, length(to_char(sal)) from scott.emp;
例如：请查询名字的字符长度为4的雇员
select* from scott.emp where length(ename) =4;
selectlength(‘李明’) from dual; –长度为2，不区分英汉，都占1个字符
说明：
the length functionsreturn the length of char. length calculates length usingcharacters as defined by the input character set.
–返回以字符为单位的长度.
lengthb usesbytes instead of characters.
–返回以字节为单位的长度.
lengthc usesunicode complete characters.
–返回以unicode完全字符为单位的长度.
length2 usesucs2 code points.
–返回以ucs2代码点为单位的长度.
length4 usesucs4 code points.
–返回以ucs4代码点为单位的长度.
下面的例子比较了不同长度计算函数的差异：
createtable s(a char(5), b nchar(5), c varchar(5), d nvarchar2(5));
insertinto s values(‘aa’,’aa’,’aa’,’aa’);
insertinto s values(‘你好’,’你好’,’你好’,’你好’);
insertinto s values(‘你好!’,’你好!’,’你好!’,’你好!’);
selectlength(a), a, length(b), length(c), length(d) from s;
selectlengthb(a),a,lengthb(b),lengthb(c),lengthb(d)from s;
selectlengthc(a),a,lengthc(b),lengthc(c),lengthc(d)from s;
8. lower（字符串）
功能：返回字符串，并将所有的字符小写。
selectlower(‘abbbccdd’) abbbccdd from dual;
9. upper（字符串）
功能：返回字符串，并将所有的字符大写。
selectupper(‘abbbccdd’) abbbccdd from dual;
10. substr(string,start, count)
功能：取子字符串，从start开始，取count个。
selectsubstr(‘13370840627’,3,5) from dual;
例如：请把雇员名字首字母小写，其他字母大写。
selectlower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from scott.emp;
11. rpad和lpad函数
功能：在列的右/左边粘贴字符
例如：显示page1要占15个字符，不足的部分左/右边用*.占位。
selectlpad(‘page 1’,15, ‘*.’) “lpad example ” from dual;
selectrpad(‘page 1’,15, ‘*.’) “rpad example ” from dual;
12. ltrim和rtrim
功能：删除左边/右边出现的字符串
举例如下：
selectltrim(‘qingdao university’, ‘q ‘) from dual;
13. soundex
功能：返回一个与给定字符串读音相同的字符串
create table table1(xm varchar(8) );
insert intotable1 values(‘weather’);
insert intotable1 values(‘wether’);
insert intotable1 values(‘goose’)
select xmfrom table1 where soundex(xm)=soundex(‘weather’);
14. trim(‘s’from ‘string’)
功能：去掉指定字符串前后的某些字符。
例如：
selecttrim(0 from 0098123400) “trim example ” from dual;
15. to_char(datetime,string format)
功能：将日期型转换为字符串。
to_char(number,stringformat)
功能：将数值转换为字符串
例如：
selectto_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) from dual;
selectename,to_char(sal, ‘l99g999d99’) from scott.emp;
说明：
9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前加美元
l：在数字前加本地货币符号
c：在数字前加国际货币符号
g：在指定位置显示组分隔符
d：在指定位置显示小数点符号
16. to_number(string)
功能：将给出的字符串转换为数字。
例如：
selectto_number(‘1999’) year from dual;
17. decode函数
功能：相当于一条if语句
举例：
create tablestudent(sno char(2), sex char(1),birthday date)– 创建学生表
insert intostudent values(’01’,’m’,’18-8月-1992′); –添加记录
insert intostudent values(’02’,’t’, ‘9-5月-1993’); –添加记录
insert intostudent values(’03’,’f’ ,’18-1月-1994′); –添加记录
insert intostudent values(’04’,null,’11-8月-1993′); –添加记录
select *from student; — 查询学生表
/*查询学生的学号和性别信息，如果性别值为m则显示male，性别值为f则显示female，如果为空值则显示unknow，否则则显示invalid*/
selectsno,sex,decode(sex, ‘m’,’male’,’f’,’female’,null,’unknow’,’invalid’)fromstudent;
举例：查询student表，统计1992、1993、1994各年出生的学生人数。
selectto_char(trunc(birthday,’year’),’yyyy’),count(*)
from student
whereto_char(trunc(birthday,’year’),’yyyy’) in (‘1994′,’1993′,’1992’)
group byto_char(trunc(birthday,’year’),’yyyy’)
在sql疑难问题中，decode函数常常发挥非常灵活的作用。其中一个就是为了某种目的将一个表的行转换成列。例如：
selectsum(decode(to_char(trunc(birthday,’year’),’yyyy’),’1994′,1,0) ) birth_1994,
sum(decode(to_char(trunc(birthday,’year’),’yyyy’),’1993′,1,0)) birth_1993,
sum(decode(to_char(trunc(birthday,’year’),’yyyy’),’1992′,1,0)) birth_1992
from student
whereto_char(trunc(birthday,’year’),’yyyy’) in (‘1994′,’1993′,’1992’)
18. greatest函数
功能：返回一组表达式中的最大值，即比较字符的编码大小。
举例：
selectgreatest(‘ad’,’ac’,’aa’) from dual;
19. least函数
功能：返回一组表达式中的最小值，即比较字符的编码大小。
举例：
selectleast(‘ad’,’ac’,’aa’) from dual;
20. uid 函数
功能：返回标识当前用户的唯一整数
举例：
show user
select uidfrom dual;
selectusername,user_id from dba_users where user_id=uid;
2 数字函数
1. abs
功能：取绝对值
selectabs(100),abs(-100) from dual;
2. acos
功能：给出反余弦的值
selectacos(-1) from dual;
3. asin
功能：给出反正弦的值
selectasin(0.5) from dual;
4. atan
功能：返回一个数字的反正切值
selectatan(1) from dual
5. ceil
功能：返回大于或等于给出数字的最小整数
selectceil(3.1415926) from dual;
6. cos
功能：返回一个给定数字的余弦
selectcos(-3.14) from dual;
7. exp
功能：返回一个数字e的n次方
selectexp(2),exp(1) from dual;
8. floor
功能：对给定的数字取整数（舍掉小数位）
selectfloor(234.56) from dual;
9. ln
功能：返回一个数字的对数值
selectln(1), ln(2) from dual;
10. log(n1,n2)
功能：返回以n1为底的n2的对数
selectlog(2,1), log(2,4) from dual;
11. mod(n1,n2)
功能：返回一个n1除以n2的余数
selectmod(10,3) mod(2,3) from dual;
12. power(n1,n2)
功能：返回n1的n2次方
selectpower(2,5) from dual;
13. round
功能：按照指定的精度进行舍入（四舍五入）
14. trunc
功能：按照指定的精度进行舍入（用于截取，没有指定截取到第几位，默认取整数）。
举例：
selectround(55.5),trunc(55.5),round(-55.5), trunc (-55.5)from dual;
selecttrunc(12.345,2), trunk(12.234,-2) from dual;
selectto_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh, to_char(trunc(sysdate,’mi’), ’yyyy.mm.dd hh24:mi:ss’) hhmm from dual;
selecttrunc(sysdate) from dual –2014-3-18今天的日期为2014-3-18
select trunc(sysdate, ‘mm’) from dual –2014-3-1返回当月第一天.
select trunc(sysdate,’yy’) from dual–2014-1-1 返回当年第一天
select trunc(sysdate,’dd’) from dual–2014-3-18 返回当前年月日
select trunc(sysdate,’yyyy’) from dual–2014-1-1 返回当年第一天
select trunc(sysdate,’d’) from dual–2014-3-16 (星期天)返回当前星期的第一天
select trunc(sysdate, ‘hh’) from dual –2014-3-1814:00:00 当前时间为14:41
select trunc(sysdate, ‘mi’) from dual –2014-3-1814:41:00 trunc()函数没有秒的精确
15. sign
功能：取数字n的符号，大于0返回1，小于0返回-1，等于0返回0
select sign(12),sign(-10),sign(0)from dual;
16. sqrt(n)
功能：返回数字n的平方根
selectsqrt(64) ,sqrt(10)from dual;
17. avg(distinct| all)
功能：返回平均值
selectavg(sal) from scott.emp;
selectavg(sal) from emp;
18. max(distinct| all)
功能：返回最大值
selectmax(sal) from scott.emp;
19. min(distinct| all)
功能：返回最小值
selectmin (sal) from scott.emp;
20. stddev(distinct| all)
功能：求标准差
selectstddev (sal) from scott.emp;
21. variance(distinct| all)
功能：求协方差
selectvariance (sal) from scott.emp;
2 日期函数
1. add_months
功能：增加或减去月份
举例：
selecthiredate, add_months(hiredate,2) from scott.emp;
selectto_char(add_months(to_date(‘199912’, ‘yyyymm’),2), ‘yyyymm’) from dual;
举例：请查询最近三个月入职的员工（把hiredate增加3个月，如果新日期大于当前日期则满足查询要求）
select *from emp where add_months(hiredate,3)>=sysdate
select *from emp where hiredate>=add_months(sysdate,-3)
2. last_day
功能：返回当月日期的最后一天
selectlast_day(sysdate) from dual;
3. months_between(date1, date2)
功能：给出date1-date2的月份
举例：
selectmonths_between (to_date(‘2013.05.20’, ‘yyyy.mm.dd’), to_date(‘2014.04.20’, ‘yyyy.mm.dd’) ) mon_betw from dual;
4. new_time(date, ‘this’, ‘that’)
功能：给出时间date在this’时区对应that’时区的日期和时间
举例：
selectto_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;
说明：this,that对应的时区及其简写，大西洋标准时间：ast或adt；阿拉斯加_夏威夷时间：hst或hdt；英国夏令时：bst或bdt；美国山区时间：mst或mdt；美国中央时区：cst或cdt；新大陆标准时间：nst；美国东部时间：est或edt；太平洋标准时间：pst或pdt；格林威治标准时间：gmt；yukou标准时间：yst或ydt。
5. next_day(date, ‘day’)
功能：给出日期date和星期x以后计算下一个星期的日期
selectnext_day(’18-5月-2013′, ‘星期五’) next_dayfrom dual; –下一个星期五是多少号？
6. to_date (string, ‘format’)
功能：将字符串转换成oracle中的一个日期（format的格式）
注意：插入date列时默认以（日-月-年）格式。
yy：两位数字的年份 2004 —> 04
yyyy：四位数字的年份 2004年
mm: 两位数字的月份 8月 –à08
dd: 2位数字的天 30号 -à30
hh24: 8点—à 20
hh12: 8点 —-à 08
mi、ss —-à显示分钟\秒
举例：
insert into scott.emp(empno,hiredate)
values(2222,to_date(‘1988-11-11’, ‘yyyy-mm-dd’))