Oracle常见函数汇总

柔情只为你懂 2023-07-12 14:43 204阅读 0赞

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 ] )
如:
None.gif substr( ‘ This is a test ‘ , 6 , 2 ) would return ‘ is ‘
None.gif substr( ‘ This is a test ‘ , 6 ) would return ‘ is a test ‘
None.gif substr( ‘ TechOnTheNet ‘ , - 3 , 3 ) would return ‘ Net ‘
None.gif 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;

发表评论

表情:
评论列表 (有 0 条评论,204人围观)

还没有评论,来说两句吧...

相关阅读

    相关 Excel面试常见函数汇总

    因为Excel在数据分析中有着重要的地位,所以我们经常在数据分析面试过程中遇到很多和Excel相关的面试题。 Excel相关面试题,主要保存:Excel基本操作相关题目、函数

    相关 oracle常用函数汇总

    以下是对oracle中的常用函数进行了汇总介绍,需要的朋友可以过来参考下 一、运算符 算术运算符:+ - \ / 可以在select 语句中使用 连接运算符:|| s

    相关 ORACLE常用函数汇总

    PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数,这些函数都可被称为SQL或PL/SQL语句, 函