oracle 常用函数

一、单行函数

1、字符函数

1.1、concat(str1,str2) 字符串拼接函数

Select concat(‘hello’,’world’) from dual;

等价于

Select ‘hello’ || ‘world’ from dual;

 

1.2、initcap(str)将每个单词首字母大写，其它字母小写

Select initcap(‘hello world’) from dual;  è’Hello World’;

Select initcap(‘HELLO WORLD’) from dual è’Hello World’;

 

1.3、instr(x,find_string[,start][,occurrence]) 返回指定字符串在某字符串中的位置，可以指定搜索的开始位置和返回第几次搜索出来的结果（这个位置是从1开始算起）

Select instr(‘Hello World’,’o’) from dual;è 5

Select instr(‘Hello World’,’o’,6) from dual;è8

Select instr(‘Hello World’,’o’,1,2) from dual;è8

Select instr(‘Hello World’,’c’) from dual; è0

Instr()函数可以充当模糊查询，当数量十万以上时，效果才慢慢的体现出来，数量越大，效果越明显，而且，当查询的字段加上索引后，instr查询的更快

Instr(字段,’xx’)>0  è  字段  like  ‘%xx%’

Instr(字段,’xx’)=1  è  字段  like  ‘xx%’

Instr(字段,’xx’)=0  è  字段  not like  ‘%xx%’

 

1.4、length(str) 返回表达式中的字符数

Select length(‘abc’) from dual; ==》3

Select length(1234) from dual; è4

 

1.5、lower(str) 将字符串转换为小写

Select lower(‘Hello World’) from dual; èhello world

 

1.6、upper(str) 将字符串转换为大写

Select upper(‘hello world’) from dual; èHELLO WORLD

 

1.7、lpad(str,width[,pad_string]) 当字符串长度不够时，左填充补齐，可以指定补齐时用什么字符补齐，若不指定，则以空格补齐

Selece lpad(‘hello world’,20) from dual; è         hello word

Select lpad(‘hello world’,20,’*’) from dual;è*********hello world

 

1.8、rpad(str,width[,pad_string])  同上

 

1.9、ltrim(x[,trim_string]) 从字符串左侧去除指定的所有字符串，若没有指定去除的字符串，则默认除去左侧空白符

Select ltrim(‘    hello world    ‘) from dual; èhello world   

Select ltrim(‘****hello world****’) from dual;èhello world****

 

1.10、rtrim(x[,trim_string]) 从字符串右侧去除指定的所有字符串，原理同ltrim()

 

1.11、trim(trim_string from x) 从字符串中两侧去除指定的所有字符串

Select trim(‘*’  from ‘***++hello world***++’) from dual;è++hello world++

注意，ltrim()和rtrim()的截取集可以使多个字符，但trim的截取集只能有一个字符

select trim('*+' from '***+*Hello World!***+*') from dual;  è报错，’*+’ 是两个字符

 

1.12、trim(str) 去除两边的空格

Select trim(‘  abc  ‘) from dual; èabc

 

1.13、nvl(x,value) 将一个null转换为另一个值，如果x的值为null，则返回value，否则返回x值本身

Select nvl(x,’值为空’) from dual;

 

1.14、nvl2(x,value1,value2) 如果x不为空，返回value1，否则返回value2

 

1.15、replace(x,search_string,replace_string) 从字符串x中搜索search_string字符串，并使用replace_string 字符串替换，并不会修改数据库中的原始值

Select replace(‘hello world’,’hello’,’hi’) from dual; èhi world

 

1.16、substr(x,start[,length])返回字符串中指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起，如果length省略，则将返回一直到字符串末尾的所有字符

Select substr(‘hello world’,3) from dual;èllo world

Select substr(‘hello world’,-3) from dual; èrld

Select substr(‘hello world’,3,2) from dual; èll

Select substr(‘hello world’,-7,4) from dual; èo wo

 

2、数值函数

2.1、abs(value) 返回value的绝对值

Select abs(-10) from dual; è10

 

2.2、ceil(value) 返回大于等于value的最小值

Select ceil(2.3) from dual; è3

 

2.3、floor(value) 返回小于等于value的最大整数

Select floor(2,3) from dual; è2

 

2.4、trunc(value,n) 对value进行截断，如果n>0,保留n位小数；n<0，则保留-n 位整数位; n=0,则去掉小数部分

Select trunc(555.666) from dual; ==》555

Select trunc(555.666,2) from dual; è555.66;

Select trunc(555.666,-2) from dual; è500

 

3、转换函数（将值从一种类型转换成另一种类型，或者从一种格式转换成另外一种格式）

3.1、to_char(x[,format])：将x转换为字符串，format为转换的格式，可以为数字格式或日期格式

Select to_char(‘1234.56’) from dual; è1234.56

Select to_char(‘12345.67’,’99,999.99’) from dual; è12,345.67

 

3.2、to_number(x[,format])：将x转换为数字，可以指定format格式

Select to_number(‘970.13’)+25.5 from dual;è995.63

Select to_number(‘-$12,345.67’,’$99,999.99’) from dual; è -12345.67

 

3.3、cast(x as type)：将x转换为指定的兼容的数据库类型

select cast(12345.67 as varchar2(10)),cast('05-7月-07' as date), cast(12345.678 as number(10,2)) from dual;è 12345.67  2007/7/5   12345.68

 

3.4、to_date(x[,format])：将x字符串转换为日期

Select to_date(‘2018/02/15 11:10:30’,’yyyy/MM/dd hh24:mi:ss’) from dual;=> 2018/2/15 11:10:30

 

4、通用函数

4.1、decode用法

decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式。

 

4.2、sign用法

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

 

二、聚集函数

1、常用函数

1.1、avg(x)：返回x的平均值

Select  avg(age)  from sc;

 

1.2、count(x)返回统计的行数

Select count(name) from sc;

 

1.3、max(x)：返回x的最大值

Select max(grade) from sc;

 

1.4、min(x)：返回x的最小值

Select min(grade) from sc;

 

1.5、sum(x)：返回x的总计值

Select sum(grade) from sc;

 

2、对分组使用聚集函数

  对分组后的行使用聚集函数，聚集函数会统计每组中的值，对于每组分别统计后统计后按返回一个值。

2.1、分组时select子句后面的列名必须与group by子句后的列名一致，除非是聚合函数

Select  deptno,avg(sal)  from  emp;è错误，因为deptno不是聚集函数，也不是group by 函数

 

2.2、不能使用聚集函数作为where子句的筛选条件

Select deptno from emp where avg(sal)>1000;è 错误

 

2.3、分组后，需要使用条件进行筛选，则使用having过滤分组后的行，不能使用where，where只能放在group by前面

Eg：select deptno, avg(sal)   from emp  where deptno<>10 group by deptno  having  avg(sal)>900