Oracle 系统函数
| 函数名称 | 返回值类型 | 说明 | 示例 | |
| 字符串函数 | ascii(str) | number | 返回str首字母的ASCII码 |
select ascii('A') from dual; --65 |
| chr(num) | char | 返回ASCII码为num的字符 |
select chr(65) from dual;--'A' select chr(97) from dual;--'a' |
|
| concat(p1,p2) | char | 将p2拼接在p1后边 |
select concat(10, 20) from dual; --1020 |
|
| initcap(p) | char | 将p的首字母大写，其他字母小写 |
select initcap('ABCD') from dual; --Abcd select initcap('abcd') from dual; --Abcd |
|
|
instr(p1,p2[,i[,j]]) |
number | 返回p2中第i位开始到p2结束这个字符串在p1中第j次出现的位置 |
select instr('aabbabab', 'ab') from dual; --'ab'在'aabbabab'中第一次出现的位置 2 select instr('aabbabab', 'ab', 3) from dual; --'ab'从'aabbabab'中的第3位开始，第一次出现的位置 5 select instr('aabbabab', 'ab', 3, 2) from dual; --'ab'从'aabbabab'中的第3位开始，第二次出现的位置 7 |
|
| instrb（p1,p2[,i[,j]]） | number | 与函数instr(p1,p2[,i[,j]])的作用相同，但是按字节计算 |
select instrb('我我要要我要我要', '我要') from dual; --'我要'在'我我要要我要我要'中第一次出现的位置 3 select instrb('我我要要我要我要', '我要', 4) from dual; --'我要'从'我我要要我要我要'中的第3个字节开始，第一次出现的位置 9 select instrb('我我要要我要我要', '我要', 3, 2) from dual; --'我要'从'我我要要我要我要'中的第3个字节开始，第二次出现的位置 9 |
|
| length(p) | number | 返回参数p的长度值 |
select length('abc') from dual; --3 select length(123) from dual; --3 |
|
| lengthb(p) | number | 返回参数p的字节数 |
select lengthb('abc') from dual; --3 select lengthb('我') from dual; --2 |
|
| lower(p) | char | 将参数p转换为小写 |
select lower('ABCD') from dual; --'abcd' select lower('Ab12') from dual; --'ab12' |
|
| upper(p) | char |
将参数p转换为大写 |
select upper('abcd') from dual; --'ABCD select upper('ab12') from dual; --'AB12' |
|
| lpad(p1,i[,p2]) | char | 在p1的左侧填充p2，使其长度为i |
select lpad(123, 5, 0) from dual; --'00123' select lpad('abc', 8, 12) from dual; --'12121abc' select lpad('abc', 8, 'ef') from dual; --'efefeabc' |
|
| ltrim(p1,p2) | char |
去掉p1左边开头连续存在的字符p2 |
select lpad(123, 5, 0) from dual; --'00123' select lpad('abc', 8, 12) from dual; --'12121abc' select lpad('abc', 8, 'ef') from dual; --'efefeabc' |
|
| rpad(p1,i[,p2]) | char | 在p1的右侧填充p2，使其长度为i |
select rpad(123, 5, 0) from dual; --'12300' select rpad('abc', 8, 12) from dual; --'abc12121' select rpad('abc', 8, 'ef') from dual; --'abcefefe' |
|
| rtrim(p1,p2) | char | 去掉p1右边结尾连续存在的字符p2 |
select rtrim('aabbcc', 'b') from dual; --'aabbcc' select rtrim('aabbcc', 'c') from dual; --'aabb' select rtrim('cdefabab', 'ab') from dual; --'cdef' |
|
| replace(p1,p2[,p3]) | char | 用p3替换掉p1中存在的p2 |
select replace('abcabc', 'c') from dual; --'abab' select replace('abcabc', 'c', 'd') from dual; --'abdabd' |
|
| reverse(p) | char | 返回字符串p的倒叙排列 |
select reverse('123') from dual; --'321' select reverse('abcd') from dual; --'dcba' |
|
| substr(p1,i[,j]) | char | 从p1中的i位置开始，截取长度为j的字符串 |
select substr('abcdefg', 3) from dual; --'cdefg' select substr('abcdefg', 2, 3) from dual; --'bcd' select substr(123456, 2, 4) from dual; --'2345' |
|
| substrb(p1,i[,j]) | char | 从p中的第i个字节开始，截取长度为j个字节的字符串 |
select substrb('123456', 2, 3) from dual; --'234' select substrb('你好，世界！', 3, 2) from dual; --'好' select substrb('你好，世界！', 3, 4) from dual; --'好，' |
|
| soundex(p) | char | 返回由4个字符组成的字符串，其中第一个字符为p的首字母，后3位为数字，可用来表示两个单词发音的相似度（忽略所有元音、字母 y、连写字母和字母 h） |
--两个单词发音一致 select soundex('sheet') from dual; --S300 select soundex('shit') from dual; --S300 --忽略元音 select soundex('ha') from dual; --H000 select soundex('hi') from dual; --H000 |
|
| translate(p1,p2,p3) | char |
用p3替换掉p1中存在的p2 |
select translate('abcabc', 'c', 'd') from dual; --'abdabd' |
|
| trim(p) | char |
删除p首部和尾部的空格 |
select trim(' abc ') from dual; --'abc' | |
| 正则表达式函数 | regexp_like(colname,pattern) | 布尔 | 与like的功能相似，如果colname列的值满足正则表达式regex，则返回该行数据 |
--查询数据表emp中ename的值以A、B开头的多有数据行 select * from emp where regexp_like(ename,'^[AB]'); |
|
regexp_instr(source,pattern [,position [,occurrence [,return_option [,match_parameter ]]]]) |
number |
与instr的功能相似 ,返回字符串中与正则表达式pattern匹配的字符的位置 |
参数说明： source：需要进行匹配的字符串 pattern：正则表达式 position：从source中开始匹配的位置 return_option： 0：返回第一个匹配字符出现的位置 1：返回匹配字符下一个字符的位置 math_parameter： i：大小写不敏感 c：大小写敏感(默认值) n：.不匹配换行符 m：多行模式 x：扩展模式，忽略正则表达式中的空白字符 select regexp_instr('abcd,aabccd,aefdacd','[^,]+') from dual;--1 select regexp_instr('abcd,aabccd,aefdacd','[^,]+',6) from dual;--6 select regexp_instr('abcd,aabccd,aefdacd','[^,]+',1,2) from dual;--6 select regexp_instr('abcd,aabccd,aefdacd','[^,]+',6,2) from dual;--13 select regexp_instr('abcd,aabccd,aefdacd','[^,]+',1,2,0) from dual;--6 select regexp_instr('abcd,aabccd,aefdacd','[^,]+',1,2,1) from dual;--12 |
|
|
regexp_substr(source,pattern [,position [,occurrence [,match_parameter ]]]) |
char | 与substr的功能相似，截取字符串中与正则表达式pattern匹配的字符 |
select regexp_substr('abcd,aabccd,aefdacd','[^,]+') from dual;--'abcd' select regexp_substr('abcd,aabccd,aefdacd','[^,]+',6) from dual;--'aabccd' select regexp_substr('abcd,aabccd,aefdacd','[^,]+',1,2) from dual;--'aabccd' select regexp_substr('abcd,aabccd,aefdacd','[^,]+',6,2) from dual;--'aefdacd' |
|
|
retexp_replace(source,pattern [,replace_string [,position [,occurrence [,match_parameter ]]]]) |
char | 与replace的功能相似 ，将符合正则表达式的字符替换为新的字符 |
select regexp_replace('abCdEFGhiJK', '[a-z]') from dual; --'CEFGJK' select regexp_replace('abCdEFGhiJK', '[a-z]', '*') from dual; --'**C*EFG**JK' select regexp_replace('abCdEFGhiJK', '[a-z]', '*', 3) from dual; --'abC*EFG**JK' select regexp_replace('abCdEFGhiJK', '[a-z]', '*', 3, 2) from dual; --'abCdEFG*iJK' |
|
| 数字函数 | abs(p) | number | 返回参数的绝对值 |
select abs(123) from dual; --123 select abs(-123) from dual; --123 |
| cos(p) | number | 返回参数的余弦值 | ||
| sin(p) | number | 返回参数的正弦值 | ||
| tan(p) | number | 返回参数的正切值 | ||
| acos(p) | number | 反余弦函数，返回-1到1之间的数 | ||
| asin(p) | number | 反正弦函数，返回-1到1之间的数 | ||
| atan(p) | number | 反正切函数，返回p的反正切值 | ||
| cosh(p) | number | 返回参数的双曲余弦值 | ||
| sinh(p) | number | 返回参数的双曲正弦值 | ||
| tanh(p) | number | 返回参数的双曲正切值 | ||
| ceil(p) | number | 返回大于等于p的最小整数 |
select ceil(1.3) from dual; --2 select ceil(1.52) from dual; --2 |
|
| floop(p) | number | 返回小于等于p的最大整数 |
select floor(1.56) from dual; --1 select floor(1.2) from dual; --1 |
|
| exp(p) | number | 返回e的p次幂，e=2.71828183 | select exp(2) from dual; --7.38905609893065 | |
| ln(p) | number | 返回p的自然对数，p>0 | select ln(2.71828183) from dual; --1.00000000056689 | |
| log(i,j) | number | 返回以i为底j的对数 | select log(10, 100) from dual; --2 | |
| mod(i,j) | number | 返回i除以j的余数 |
select mod(10, 3) from dual; --1 select mod(12, 5) from dual; --2 |
|
| power(i,j) | number | 返回i的j次方 |
select power(10, 2) from dual; --100 select power(5, 2) from dual; --25 |
|
| round(i[,j]) | number | 返回对i进行四舍五入后的值，j是保留小数位（可以是0、整数、负数） |
select round(123.456) from dual; --123 select round(123.456, 2) from dual; --123.46 select round(123.456, -2) from dual; --100 |
|
| sign(p) | number | p=0，返回0；p>0，返回1；p<0，返回-1 |
select sign(0) from dual; --0 select sign(10) from dual; --1 select sign(-2) from dual; -- -1 |
|
| sqrt(p) | number | 返回参数的平方根 |
select sqrt(100) from dual; --10 select sqrt(25) from dual -- 5 |
|
| trunc(i,j) | number | 返回i保留j位小数的值，不进行四舍五入（j可以是0、整数、负数） |
select trunc(123.456) from dual; --123 select trunc(123.456, 2) from dual; --123.45 select trunc(123.456, -2) from dual; --100 |
|
| 转换函数 | convert(p,ds[,ss]) | char | 将参数从ss字符集转换为ds字符集 |
select convert('你好', 'utf8', 'zhs16gbk') from dual; --'浣犲ソ' select convert('浣犲ソ', 'zhs16gbk', 'utf8') from dual; --'你好' |
| hextoraw(p) | char | 将16进制的参数转换为raw类型 | select hextoraw('abcdef') from dual; --'ABCDEF' | |
| rawtohex(p) | char | 将raw类型的参数转换为16进制 |
select rawtohex('AA') from dual; --'4141' select rawtohex('aa') from dual; --'6161' |
|
| rawidtochar(p) | char | 将rawid类型的参数转换为char类型 | ||
| cast | 不定 | 将某种类型转换为另一种类型 |
select cast('123' as number(6,2)) from dual; --123.00 |
|
| to_char(p[,fmt]) | char | 将参数p转换为char类型，如果p为date类型，则可以将其转换为指定格式的字符 |
select to_char(123) from dual; --'123' select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; --'2017-09-06 21:48:11' |
|
| to_date(s,fmt) | date | 将字符转换为指定格式的date类型 |
select to_date('2017-09-06', 'YYYY-MM-DD') from dual; --'2017.9.6 select to_date('09-06-2017', 'MM-DD-YYYY') from dual; --'2017.9.6' select to_date('2017-09-06 21:48:11', 'YYYY-MM-DD HH24:MI:SS') from dual; --'2017.9.6 21:48:11' |
|
| to_multi_byte(p) | char | 将单字节字符转换为双字节字符 |
select lengthb('a') from dual; --1 select lengthb(to_multi_byte('a')) from dual; --2 |
|
| to_number(p) | number | 将参数转换为数字 | select to_number('123') from dual; --123 | |
| to_single_byte() | char | 将双字节字符转换为单字节字符 |
select lengthb('a') from dual; --1 select lengthb(to_multi_byte('a')) from dual; --2 select lengthb(to_single_byte(to_multi_byte('a'))) from dual; --1 |
|
| 日期函数 | add_months(d,i) | date | 获取在日期d上加i个月后的日期 |
select add_months(to_date('2017-09-06', 'YYYY-MM-DD'), 2) from dual; --2017.11.6 |
| last_day(d) | date | 获取日期d中月份最后一天的日期 |
select last_day(to_date('2017-02-06', 'YYYY-MM-DD')) from dual; --2017.2.28 select last_day(to_date('2017-09-06', 'YYYY-MM-DD')) from dual; --2017.9.30 |
|
| months_between(d1,d2) | number |
获取日期d1、d2相差几个月 |
select months_between(to_date('2017-02-06', 'YYYY-MM-DD'), to_date('2017-09-06', 'YYYY-MM-DD')) from dual;-- -7 select months_between(to_date('2017-09-06', 'YYYY-MM-DD'), to_date('2017-02-06', 'YYYY-MM-DD')) from dual;-- 7 |
|
| new_time(d,tz1,tz2) | date | 将日期d从tz1时区转换为tz2时区 | select new_time(sysdate, 'GMT', 'CST') from dual; --2017.9.6 16:38:47 | |
| next_day(d,w) | date | 获取日期d后的下一个星期w（1-7对应周日-周六） |
select next_day(to_date('2017-09-06'), 4) from dual; --'2017.9.13' select next_day(to_date('2017-09-06'),'星期三') from dual; --'2017.9.13' |
|
| round(d,fmt) | date | 返回日期d按照指定格式舍入后日期的第一天 |
select round(to_date('2017-05-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2017.1.1' select round(to_date('2017-09-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2018.1.1' select round(to_date('2017-09-06', 'YYYY-MM-DD'), 'MM') from dual; --'2017.9.1' select round(to_date('2017-09-26', 'YYYY-MM-DD'), 'MM') from dual; --'2018.10.1' |
|
| trunc(d,fmt) | date | 返回日期d按照指定格式截取后日期的第一天 |
select trunc(to_date('2017-05-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2017.1.1' select trunc(to_date('2017-09-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2017.1.1' select trunc(to_date('2017-09-06', 'YYYY-MM-DD'), 'MM') from dual; --'2017.9.1' select trunc(to_date('2017-09-26', 'YYYY-MM-DD'), 'MM') from dual; --'2018.9.1' |
|
| sysdate | date | 获取当前系统时间 |
select sysdate from dual; --'2017-09-06 22:13:14' |
|
| extract | char | 截取日期中的指定字段 |
-- 获取日期中的年份 select extract(year from sysdate) from dual; --'2017' select extract(year from date'2011-01-03') from dual; --'2011' -- 获取日期中的月份 select extract(month from date'2011-01-03') from dual; --'1' -- 获取日期中的天 select extract(day from date'2011-01-03') from dual; --'3' |
|
| 聚合函数 | avg(col) | number | 返回col列的平均值 |
select avg(sal) from emp; select avg(sal) from emp group by deptno; |
| count(col|*) | number | 返回列col的行数，*表示所有行 |
select count(ename) from emp; |
|
| max(col) | 不定 | 返回列col中的最大值 |
select max(sal) from emp; |
|
| min(col) | 不定 | 返回列col中的最小值 |
select min(sal) from emp; |
|
| stddev(col) | number | 返回列col的标准差，标准差是方差的平方根 |
select stddev(sal) from emp; |
|
| sum(col) | number | 返回列col的总和 |
select sum(sal) from emp; |
|
| variance(col) | number | 返回列col的统计方差 |
select variance(sal) from emp; |
|
| wm_concat(col) | char | 返回列col的合并行，用逗号分隔 |
--获取每个部门中的员工姓名 select wm_concat(ename) from emp group by deptno; |
|
| nvl(col,p) | char | 如果col的值为空，就用p代替 |
select nvl(null,'HH') from dual; --'HH' |
|
| decode(col,p1,s1,p2,s2...sn) | char | 为指定列的每个值指定一个对应的新值，如果没有指定 |
select e.*,decode(deptno,10,'1班',20,'2班',30,'3班',40,'4班','没有对应班级') from emp e; 结果： ... ... dept decode ... ... 10 1班 ... ... 20 2班 ... ... 30 3班 ... ... 40 4班 ... ... 没有对应班级 |
|