Oracle常用函数

一、 单行函数

只处理单个行，且一个函数只返回一个结果

1、 concat(str1,str2)字符串拼接

select ‘Hello ‘||‘World‘ from dual;

等价于

select concat(‘Hello‘,‘ World‘) from dual;

注：

dual可以理解为oracle中存在的一张虚拟表，因为sql命令强制要求数据从某表中取得，也就是说sql语句中必须要有from命令，所以oracle定义了虚拟表dual，提供一些特殊字段的查询，例如时间日期、算术运算等功能。

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

select initcap(‘hello world!‘) from dual;

--返回结果为‘Hello World!‘

select initcap(‘HELLO WORLD!‘) from dual;

--返回结果为‘Hello World!‘

3、 instr(x,find_string[,start][,occurrence])

返回指定字符串在某字符串中的位置，可以指定搜索的开始位置和返回第几次搜索出来的结果

start:开始位置

occurrence:返回第几次搜索出的结果

无结果返回0

select instr(‘Hello World!‘,‘o‘) from dual;

--从1位置开始搜索，返回第一次出现的o的位置，结果为5

select instr(‘Hello World!‘,‘o‘,6) from dual;

--从6位置开始搜索，返回第一次出现的o的位置，结果为8

select instr(‘Hello World!‘,‘o‘,1,2) from dual;

--从1位置开始搜索，返回第二次出现o的位置，结果为8

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

select length(‘hello world!‘) from dual;

--返回结果为12

select length(‘张三‘) from dual;

--返回结果为2

5、 lengthb(str)返回表达式中的字节数

字符集为UTF8，一个汉字三个字节

字符集为ZHS16GBK，一个汉字两个字节

select lengthb(‘张三‘) from dual;

--返回结果为6

select lengthb(‘hello world!‘) from dual;

--返回结果为12

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

select lower(‘HELLO WORLD!‘) from dual;

--返回结果为hello world!

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

select upper(‘hello world!‘) from dual;

--返回结果为HELLO WORLD!

8、 lpad(str,width[,pad_string])

当字符串长度不够时，左填充补齐，可以指定补齐时用什么字符补齐,若不指定，则以空格补齐

select lpad(‘hello‘,10,‘*‘)

--返回结果为*****hello

9、 rpad(str,width[,pad_string])

当字符串长度不够时，右填充补齐，原理同左填充

原理同8、lpad()

10、 ltrim(x[,trim_string])

从字符串左侧去除指定的所有字符串，若没有指定去除的字符串，则默认去除左侧空白符

注：第二个参数的每一个字符都到第一个参数中去匹配

select ltrim(‘*++*-**hello‘,‘-+*‘)

--返回结果为hello

11、        rtrim(x[,trim_string])

从字符串右侧去除指定的所有字符串

原理同10、ltrim()

12、        trim(trim_string from x)

原理同10、11

select trim(‘*+‘ from ‘***+*Hello World!***+*‘) from dual;

--返回结果Hello World!

13、        nvl(x,value)

将一个NULL转换为另外一个值，如果x为NULL,则返回value,否则返回x值本身

select * from SCDLTEST.TEST1207

select id,nvl(name,‘哈哈‘) from SCDLTEST.TEST1207

14、  nvl2(x,value1,value2)

如果x不为NULL，返回value1，否则，返回value2

select id,nvl2(name,‘有姓名‘,‘无姓名‘) from SCDLTEST.TEST1207

15、 replace(x,search_string,replace_string)

从字符串x中搜索search_string字符串，并使用replace_string字符串替换。并不会修改数据库中原始值

select * from SCDLTEST.TEST1207

select replace(name,‘a‘,‘b‘) from SCDLTEST.TEST1207

16、 substr(x,start[,length])

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

select * from SCDLTEST.TEST1207

select id,substr(name,1,2) from SCDLTEST.TEST1207

二、 数值函数

1、        abs(value)

返回value的绝对值

select abs(-10)

--返回结果为10

2、        ceil(value)

返回大于等于value的最小整数

select ceil(2.3)

--返回结果为3

3、        floor(value)

返回小于等于value的最大整数

与2同理

4、        trunc(value,n)

对value进行截断，如果n>0，保留n位小数；n<0，则保留-n位整数位；n=0，则去掉小数部分

select trunc(555.666)

--返回值555，不加第二个参数默认去掉小数

select trunc(555.666,2)

--返回值555.66，保留小数点右侧2位

select trunc(555.666,-2)

--返回值500，截取小数点左侧2位

5、        round(value,n)

对value进行四舍五入，保存小数点右侧的n位。如果n省略的话，相当于n=0的情况

与trunc类似，不过会四舍五入

select round(555.666);

--返回结果为556，不加n时默认去掉小数部分

select round(555.666,2);

--返回结果为555.67

select round(555.666,-2);

--返回结果为600

三、 转换函数

1、        to_char(x[,format])

将x转化为字符串。 format为转换的格式，可以为数字格式或日期格式

select to_char(‘12345.67‘,‘99,999.99‘) from dual;

--返回结果为12,345.67

注：format gauss100不支持99,999.99格式

2、        to_number(x [,  format])

将x转换为数字。可以指定format格式

select to_number(‘970.13‘) + 25.5;

--返回值为995.63

3、        cast(x as type)

将x转换为指定的兼容的数据库类型

select cast(12345.67 as varchar2(10)),cast(‘2015-7-07‘ as date), cast(12345.678 as number(10,2)) from dual;

返回结果：

4、        to_date(x [,format])

将x字符串转换为日期

select to_date(‘2012-3-15‘,‘YYYY-MM-DD‘)

--返回结果2012-03-15 00:00:00

四、 聚合函数

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

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

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

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

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

Oracle常用函数

原文：https://www.cnblogs.com/zhuyunlong/p/12171701.html