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;
还没有评论,来说两句吧...