Oracle常见函数汇总-蒲公英云

1 INITCAP()

假设c1为一字符串.函数INITCAP()是将每个单词的第一个字母大写,其它字母变为小写返回.

单词由空格,控制字符,标点符号等非字母符号限制.

select initcap(‘hello world’) from dual;

结果 Hello World

2 Concat

连接字符串

`select` `concat(‘CSDN’,‘_yeeXun’)from`dual;

结果 CSDN_yeeXun

3 ||

连接字符串

select 'CSDN' || '_yeeXun' from dual; 结果 CSDN_yeeXun contact 与 || 区别 CONCAT只能连接两个字符串，||可以连接多个字符串例如

select concat( 'CSDN' , '_yeeXun' , 'china' ) from dual

ORA-00909: invalid number of arguments

select 'CSDN' || '_yeeXun' || 'china' from dual; 结果 CSDN_yeeXunchina 当然 contact可以通过在sql语句里边通过多次调用该函数来实现连接多个字符串例如 select concat(concat('aa','bb'),'cc') from dual;

4 substr 取得字符串中指定起始位置和长度的字符串 substr( string, start_position, [ length ] )
如:
substr( ‘ This is a test ‘ , 6 , 2 ) would return ‘ is ‘
substr( ‘ This is a test ‘ , 6 ) would return ‘ is a test ‘
substr( ‘ TechOnTheNet ‘ , - 3 , 3 ) would return ‘ Net ‘
substr( ‘ TechOnTheNet ‘ , - 6 , 3 ) would return ‘ The ‘

select substr(‘Thisisatest’, -4, 2) value from dual 结果是 te

select substr(‘emros’,-3,1) value from dual 结果是 r

5 length 函数函数本身挺简单，在这介绍两个疑点
问题1 oracle中的函数length(char),为什么可以写成length(123),length(‘abc’)，但是length(123asd)和length(asd)就不行了？
解答：length(123)=3,是计算123的长度
length(‘abc’)=3,是计算’abc’的长度
length(123asd)和length(asd)中,函数将其当做是变量,因为它既不是有效的数字,也不是有效的字符串(如’aaaaaa’,带引号的),所以就会出现”标识符无效”等错误。
问题2 在oracle里出现的,取数据库中一个字符类型列的长度,是个类别字段用的是char(10) bigcata
如大类都是两位数 01,02等类别不多小类就是0101,0102,0201,0202
本来我想 select xxx from xxx where length(bigcata ) = 2 没结果
select bigcata , length(bigcata )from xxx 结果全是10
01 10
02 10
测试了下varchar2类型的却是好的(bigcata 的类型改成 varchar2(10))
123aa 5
aa2 3
是不是在oracle中 length()函数对char类型无效呢
解答：先trim（）再取length（）
select xxx from xxx where length(trim(bigcata) ) = 2
因为char是固定长度的如果长度不够后面要补空格的

6 instr函数
instr( string1, string2, start_position,nth_appearance )

string1	源字符串，要在此字符串中查找。
string2	要在string1中查找的字符串。
start_position	代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
nth_appearance	代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

注意：
　　位置索引号从1开始。
　　如果String2在String1中没有找到，instr函数返回0。
　　示例：
　　SELECT instr(‘syranmo’,’s’) FROM dual; — 返回 1
　　SELECT instr(‘syranmo’,’ra’) FROM dual; — 返回 3
　　SELECT instr(‘syran mo’,’a’,1,2) FROM dual; — 返回 0

7 lpad函数

语法格式如下：

lpad( string, padded_length, [ pad_string ] )

String 准备被填充的字符串；
padded_length 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。
示例一：
SQL> select lpad(‘abcde’,10,’x’) from dual;
输出 xxxxxabcde
示例二：
SQL> select lpad(‘abcde’,10,’oq’) from dual;
输出 oqoqoabcde
示例三：
SQL> select lpad(‘abcde’,2) from dual;
输出 ab

8 rpad函数
与lpad函数对应，rpad函数从右边对字符串使用指定的字符进行填充，语法格式与lpad格式相同：
rpad( string, padded_length, [ pad_string ] )

例如：
rpad(‘tech’, 7); 将返回’tech ‘
rpad(‘tech’, 2); 将返回’te’
rpad(‘tech’, 8, ‘0’); 将返回’tech0000’
rpad(‘tech on the net’, 15, ‘z’); 将返回 ‘tech on the net’
rpad(‘tech on the net’, 16, ‘z’); 将返回 ‘tech on the netz’

9 round函数
语法格式：
ROUND(number, num_digits)

number 必需。要四舍五入的数字。
num_digits 必需。位数，按此位数对 number 参数进行四舍五入。
ROUND函数中：

如果 num_digits 大于 0（零），则将数字四舍五入到指定的小数位。

如果 num_digits 等于 0，则将数字四舍五入到最接近的整数。

如果 num_digits 小于 0，则在小数点左侧进行四舍五入。

=ROUND(2.15, 1)	将 2.15 四舍五入到一个小数位	2.2
=ROUND(2.149, 1)	将 2.149 四舍五入到一个小数位	2.1
=ROUND(-1.475, 2)	将 -1.475 四舍五入到两个小数位	-1.48
=ROUND(21.5, -1)	将 21.5 四舍五入到小数点左侧一位	20

10 trunc 函数
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
语法格式：
TRUNC（number[,decimals]）

其中：

number 待做截取处理的数值

decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分

下面是该函数的使用情况：

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89

TRUNC（89.985，-1）=80

11 Months_between函数

MONTHS_BETWEEN (x, y)用于计算x和y之间有几个月。如果x在日历中比y早，那么MONTHS_BETWEEN()就返回一个负数。

当x 和 y 之间的月份之差不是整月的时候，可以采用小数表示，例如

SELECT MONTHS_BETWEEN(‘25-MAY-2008’, ‘15-JAN-2008’)

FROM dual;

MONTHS_BETWEEN(‘25-MAY-2008’,’15-JAN-2008’)

4.32258065

months_between(date1, date2),必须注意的是，date1与date2都为Date类型，不然会出现错误。须用to_date(‘’,’’) 来转换为日期格式，才能参加计算。

例： select months_between(to_date(‘25-05-2009’,’dd-MM-yyyy’),to_date(‘25-01-2008’,’dd-MM-yyyy’)) from dual 输出 16

12 Add_months 函数
select add_months(sysdate,1) from dual

to_char ,to_number,to_date函数在此不再介绍
举例如下：
select to_char(sysdate,’yyyy-mm-dd’) from dual;
select to_number(‘13’)+to_number(‘14’) from dual
Select to_date(‘20090210’,‘yyyyMMdd’) from dual

13 NVL函数

格式为：

NVL( string1, replace_with)

功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

注意事项：string1和replace_with必须为同一数据类型，除非显式的使用TO_CHAR函数进行类型转换。

14 NVL2()函数

NVL2(expr1,expr2,expr3)

Oracle在NVL()的功能上扩展，提供了NVL2函数。

功能：oracle中常用函数，如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。

即：NVL2(表达式,不为空设值,为空设值)

15 COALESCE()函数
依次考察各参数表达式，遇到非null 值即停止并返回该值。
select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)总收入from emp;