目录
concat、substr、length、instr、lpad、rpad、trim、replace
一、函数介绍
SQL函数有单行函数和多行函数，其区别为：
单行：输入一行，返回一行
多行：输入多行，返回一行
单行函数：
- 操作数据对象
- 接受函数返回一个结果
- 参数可以是列、值、表达式
- 只对一行操作，且每行返回一个结果
- 可以转换数据类型，进行计算和格式化，可以嵌套
- 可分为字符函数、数字函数、日期函数、转换函数
- function_name[(arg1,arg2,...)]
二、函数分类
2、1 字符函数
字符函数可分为：
大小写转换函数：lower (小写)，upper (大写)，initcap (首字母大写)。
字符处理函数：
lower、upper、initcap
例：
select empno,ename from emp where ename=upper('scott');
EMPNO ENAME
---------- ----------
7788 SCOTT
select ename,lower(ename),upper(ename),initcap(ename) from emp;
ENAME LOWER(ENAM UPPER(ENAM INITCAP(EN
---------- ---------- ---------- ----------
SMITH smith SMITH Smith
ALLEN allen ALLEN Allen
WARD ward WARD Ward
......
注：在inticap中，空格、下划线、%、$、!等特殊符号、标点符号均可作为分隔符
例：
select initcap('chongqing,%cheng_du,wu$han') from dual;
INITCAP('CHONGQING,%CHENG_
--------------------------
Chongqing,%Cheng_Du,Wu$Han注：
在ORACLE中，
DUAL 表是一个“伪表”（虚拟表）；DUAL 只包含一个列 DUMMY，数据类型为 VARCHAR2(1)；DUAL 只包含一行，其 DUMMY 列的值为 X；DUAL 表是为了语法上的完整性。例：select 1+1 from dual;1+12
concat、substr、length、instr、lpad、rpad、trim、replace
substr(string string, int a, int b):
string 需要截取的字符串 ；a 截取字符串的开始位置（注：当 a 等于 0 或 1 时，都是从第一位开始截取）；b 要截取的字符串的长度substr(string string, int a)：从a开始截取后面所以字符
当a为负数时代表从倒数第几位开始
例：
select concat(concat(ename,' is '),job) from emp;
CONCAT(CONCAT(ENAME,'IS
-----------------------
SMITH is CLERK
ALLEN is SALESMAN
WARD is SALESMAN
select substr('oracle',1,3) from dual;
SUB
---
ora
select substr('oracle',-3,2) from dual;
SU
--
cl
select substr('oracle',4) from dual;
SUB
---
cle
SCOTT@prod>select substr('oracle',-3) from dual;
SUB
---
cleselect length('oracle') from dual;
LENGTH('ORACLE')
----------------
6
select instr('oracle','a') from dual;
INSTR('ORACLE','A')
-------------------
3
select instr('oracle','le') from dual;
INSTR('ORACLE','LE')
--------------------
5
select instr('oracle','nice') from dual;
INSTR('ORACLE','NICE')
----------------------
0instr的另外用法
当instr结果为0时代表该字符/字符串不存在，常用做判断
instr（string, str, a, b）
查询在string中，从第a(可为负数)个字符开始第b次出现str的位置
select instr('i love china','i',1,2) from dual;
INSTR('ILOVECHINA','I',1,2)
---------------------------
10lpad、rpad(string，a，b)
在string的左/右第a位补充b
trim:
去除空字符
从***中去除*：trim(character from string) ，character只能为一个字符
trim(leading/trailing character from string):从string的前/后去除character
select trim(leading/trailing 'h' from 'hello'/'helloh') from dual
replace（string，A，B）
将string中的A用B替换
replace(string，str): 替换掉string中所有str
select sal,lpad(sal,7,1),rpad(sal,6,0) from emp;
SAL LPAD(SAL,7,1) RPAD(SAL,6,0)
---------- ---------------------------- ------------------------
800 1111800 800000
1600 1111600 160000
1250 1111250 125000
select length(' hello '),length(trim(' hello ')) from dual;
LENGTH('HELLO') LENGTH(TRIM('HELLO'))
--------------- ---------------------
7 5
select trim('*' from '***oracle***') from dual;
TRIM('
------
oracle
select replace('popping', 'p') from dual;
REPL
----
oing2.2 、数字函数
round：四舍五入
trunc：截取，取整
mod：模，取余数
round
round(A，B)：
1、B>=0，对A保留B位小数，针对第B+1个小数的值进行四舍五入；
2、B<0，小数点往前移B位后的四舍五入
例：
select round(12.3265,3),round(12.3256,0),round(12.3256,-1),round(161.12,-2) from dual;
ROUND(12.3265,3) ROUND(12.3256,0) ROUND(12.3256,-1) ROUND(161.12,-2)
---------------- ---------------- ----------------- ----------------
12.327 12 10 200trunc
trunc直接截断取整，不进行四舍五入，截取方式同round
trunc(A)=trunc(A，0)
select trunc(12.3265,3),trunc(12.3256,0),trunc(12.3256,-1),trunc(161.12,-2) from dual;
TRUNC(12.3265,3) TRUNC(12.3256,0) TRUNC(12.3256,-1) TRUNC(161.12,-2)
---------------- ---------------- ----------------- ----------------
12.326 12 10 100mod
trunc(A/B)：求商quotient
mod(A，B)：求余数remainder
select trunc(16/3),mod(16,3) from dual;
TRUNC(16/3) MOD(16,3)
----------- ----------
5 12.3、日期函数
months_between：两个日期相差的月数
add_months：向指定日期中增加月
next_day：指定日期的下一个日期
last_day：当前月的最后一天
round、trunc：日期四舍五入、日期截断
简介
ORACLE默认日期显示格式为：DD-MON-RR
如：12-OCT-22，而DD-MON-YY=12-OCT-2022
查询数据库当前时间：
select sysdate from dual;
SYSDATE
---------
14-OCT-22
查询数据库默认日期格式：
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='NLS_DATE_FORMAT';
PROPERTY_NAME
PROPERTY_VALUE
NLS_DATE_FORMAT
DD-MON-RR
查询当前会话日期格式和更改当前会话日期格式：
查询：
select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
PARAMETER
----------------------------------------------------------------
VALUE CON_ID
---------------------------------------------------------------- ----------
NLS_DATE_FORMAT
DD-MON-RR 0
更改：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SCOTT@prod>select sysdate from dual;
SYSDATE
-------------------
2022-10-14 10:25:53
注：当前会话的日期格式更改后，查询数据库时只要为date格式的值均按设置的格式显示日期的算术计算：
sysdate+1: 一天以后
sysdate+1/24: 一小时后
sysdate+1/24/60: 一分钟后
两个日期相减表示日期间相差的天数
months_between
select months_between(sysdate,'2012-10-14 10:25:53') from dual;
MONTHS_BETWEEN(SYSDATE,'2012-10-1410:25:53')
--------------------------------------------
120
SCOTT@prod>select months_between('2022-08-01','2012-08-01') from dual;
MONTHS_BETWEEN('2022-08-01','2012-08-01')
-----------------------------------------
120add_months
add_months（date，int A）
A>0: date加A个月后
A<0: date减去A个月后
select add_months(sysdate,6) from dual;
ADD_MONTHS(SYSDATE,
-------------------
2023-05-14 10:58:17
SCOTT@prod>select add_months(sysdate,-6) from dual;
ADD_MONTHS(SYSDATE,
-------------------
2022-05-14 10:58:29next_day、last_day
next_day(date，A)
A可以为1,2,3,4,5,6,7或者Monday....Sunday
注：国外周日代表每周第一天，所以用1可以代表Sunday
select next_day(sysdate,1),next_day(sysdate,'sunday') from dual;
NEXT_DAY(SYSDATE,1) NEXT_DAY(SYSDATE,'S
------------------- -------------------
2022-11-20 11:07:41 2022-11-20 11:07:41
last_day
select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2022-11-30 11:10:33round、trunc
round(date)：精确到天
round(date，A)：A表示需要精确到的位置如：A='Mi' 'DD' 'ss'
trunc(date，A)：截取后的值为0
select sysdate,round(sysdate),round(sysdate,'DD'),round(sysdate,'hh'),round(sysdate,'mi') from dual;
SYSDATE ROUND(SYSDATE) ROUND(SYSDATE,'DD') ROUND(SYSDATE,'HH') ROUND(SYSDATE,'MI')
------------------- ------------------- ------------------- ------------------- -------------------
2022-11-14 11:18:47 2022-11-14 00:00:00 2022-11-14 00:00:00 2022-11-14 11:00:00 2022-11-14 11:19:00
select sysdate,trunc(sysdate),trunc(sysdate,'DD'),trunc(sysdate,'HH') from dual;
SYSDATE TRUNC(SYSDATE) TRUNC(SYSDATE,'DD') TRUNC(SYSDATE,'HH')
------------------- ------------------- ------------------- -------------------
2022-11-14 11:26:13 2022-11-14 00:00:00 2022-11-14 00:00:00 2022-11-14 11:00:002.4、转换函数
转换函数的主要作用：解决不同数据类型间的转换
手动进行的转换为显式转换，其使用的函数有：
1、字符>数值：TO_NUMBER
2、数值>字符：TO_CHAR
3、字符>日期：TO_DATE
同理数据库存在隐式转换，如在SQL中对数值使用单引号进行查询，数据库会自动转换为数值。数据库自动完成的转换有：
1、字符>日期
2、字符>数值
3、数值>字符
4、日期>字符
to_char
带有日期的to_char
to_char(date，‘format_model’)
- 必须包含在单引号中
- 大小写敏感
- 可以包含任意的有效日期格式
- 可以使用fm去除多余的空格或者前导0
- 与日期值用逗号隔开
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss PM') from dual;
TO_CHAR(SYSDATE,'YYYY-
----------------------
2022-11-15 09:56:29 AM
select to_char(sysdate,'FMDay,"the" ddth "of" Month,yyyy') from dual;
TO_CHAR(SYSDATE,'FMDAY,"THE"DDTH"OF"MONTH,YYYY')
------------------------------------------------------------------------------------------
Tuesday,the 15th of November,2022日期格式掩码：
YYYY：完整的年份，如2022
YEAR：年（英文），如twenty twenty-two
MM：双位数字月份，如11
MONTH：完整的月份（英文），如November
MON：月份英文三个字母缩写，如Nov
DY：星期三个字母缩写，如Tue
DAY：完整的星期英文
DD：月份数字天
带有数字的to_char
to_char(Number，‘format_model’)
- 9 代表一个数字
- 0 强制显示0
- $ 放置一个浮动美元符号
- L 采用浮动本地货币符号
- . 打印小数点
- , 打印逗号代表千位表示符号
- D也代表小数点，G代表逗号，但是DG只能成对出现
- 位数不够长会出现#乱码
select to_char(12345.63,'$99,999.00') from dual;
TO_CHAR(123
-----------
$12,345.63
select to_char(12345.63,'$99G999D00') from dual;
TO_CHAR(123
-----------
$12,345.63
select to_char(12345.63,'$999.00') from dual;
TO_CHAR(
--------
########to_number
select to_number('$12,345.63','$99,999.00') from dual;
TO_NUMBER('$12,345.63','$99,999.00')
------------------------------------
12345.63
select to_number('$12345.63','$99999.00') from dual;
TO_NUMBER('$12345.63','$99999.00')
----------------------------------
12345.63to_date
select ename,hiredate from emp where hiredate>='01-MAY-87';
ENAME HIREDATE
---------- ---------
ADAMS 23-MAY-87
select ename,hiredate from emp where hiredate>=to_date('1987-05-01','yyyy-mm-dd');
ENAME HIREDATE
---------- ---------
ADAMS 23-MAY-872、5通用函数
NVL函数
NVL将空值转换为已知的值，可以使用的数据类型为：数字、日期、字符
数据类型必须匹配
prod>select ename ,sal,nvl(comm,0) from emp;
ENAME SAL NVL(COMM,0)
---------- ---------- -----------
SMITH 800 0
ALLEN 1600 300
WARD 1250 500
JONES 2975 0
MARTIN 1250 1400
BLAKE 2850 0
select ename,mgr,nvl(to_char(mgr),'No Manager') from emp;
ENAME MGR NVL(TO_CHAR(MGR),'NOMANAGER')
---------- ---------- ----------------------------------------
SMITH 7902 7902
MARTIN 7698 7698
BLAKE 7839 7839
CLARK 7839 7839
SCOTT 7566 7566
KING No ManagerNVL2
NVL2(expr1,expr2,expr3)如果参数 1 非空 not null ，则返回参数 2 的值，否则返回参数 3 的值
select ename,sal,comm,sal+nvl(comm,0),nvl2(comm,sal+comm,sal) from emp;
ENAME SAL COMM SAL+NVL(COMM,0) NVL2(COMM,SAL+COMM,SAL)
---------- ---------- ---------- --------------- -----------------------
SMITH 800 800 800
ALLEN 1600 300 1900 1900
WARD 1250 500 1750 1750
JONES 2975 2975 2975
MARTIN 1250 1400 2650 2650nullif
nullif(expr1,expr2)比较两个表达式是否相同，如果相同，返回 null, 如果不同，返回 expr1expr1和expr2数据类型必须一样
select nullif(1,1) from dual;
NULLIF(1,1)
-----------
select nullif(1,2) from dual;
NULLIF(1,2)
-----------
1
select nullif('a','b') from dual;
N
-
a
select nullif('a','a') from dual;
N
-
条件表达式：decode
在使用if-then-else逻辑时
decode（col/expression,search1,result1
search2,result2,....,
,.........,default）
工作是 ANALYST 的，工资涨 10%；
工作是 CLERK 的，工资涨 15%；
工作是 MANAGER 的，工资涨 20%；
其他的涨 25%
select empno,ename,job,sal, decode(job,'ANALYST', sal*1.1, 'CLERK', sal*1.15, 'MANAGER', sal*1.20, sal*1.25) new_sal from emp order by job;
EMPNO ENAME JOB SAL NEW_SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 3000 3300
7902 FORD ANALYST 3000 3300
7934 MILLER CLERK 1300 1495
7900 JAMES CLERK 950 1092.5
7369 SMITH CLERK 800 920
7876 ADAMS CLERK 1100 1265
7698 BLAKE MANAGER 2850 3420
7566 JONES MANAGER 2975 3570
7782 CLARK MANAGER 2450 2940
7839 KING PRESIDENT 5000 6250
7844 TURNER SALESMAN 1500 1875
7654 MARTIN SALESMAN 1250 1562.5
7521 WARD SALESMAN 1250 1562.5
7499 ALLEN SALESMAN 1600 2000
14 rows selected.
comm,decode(comm,null,0,comm) from emp;
COMM DECODE(COMM,NULL,0,COMM)
---------- ------------------------
0
300 300
500 500
0
1400 1400
0
0
0
0
0 0
0
0条件表达式：case
在使用if-then-else逻辑时
case expr when comparison_expr1 then return_expr1
when comparison_expr2 then return_expr2
.....
else else_expr
select empno,ename,sal,job, case job when 'ANALYST' then sal*1.1
2 when 'CLERK' then sal*1.15
3 when 'MANAGER' then sal*1.2
4 else sal*1.25 end new_sal from emp order by job;
EMPNO ENAME SAL JOB NEW_SAL
---------- ---------- ---------- --------- ----------
7788 SCOTT 3000 ANALYST 3300
7902 FORD 3000 ANALYST 3300
7934 MILLER 1300 CLERK 1495
7900 JAMES 950 CLERK 1092.5
7369 SMITH 800 CLERK 920
7876 ADAMS 1100 CLERK 1265
7698 BLAKE 2850 MANAGER 3420
7566 JONES 2975 MANAGER 3570
select comm,case when comm is null then 0 else comm end new_comm from emp;
COMM NEW_COMM
---------- ----------
0
300 300
500 500
0
1400 1400
0
0
0
843
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
