Table of Contents
常用字符函数：
备注:测试数据库版本为Oracle 11g R2
常用字符函数：
concat 仅支持2个字符串的连接，使用得不多
|| 支持多个字符串的连接
通过字符串连接函数||和select语句结合，可以批量生成sql语句，提升效率
SQL> select concat('a','bc') from dual;
CONCAT('A','BC')
----------------
SQL> select concat('a','bc','def') from dual;
select concat('a','bc','def') from dual
ORA-00909: 参数个数无效
SQL> select 'a'||'bc'||'def' from dual;
'A'||'BC'||'DEF'
----------------
abcdef
二.greatest,least函数
greatest
可以跟若干参数(类型可为数字、字符、日期等)，返回其中最大值，若有一个null，则返回null
least 同greatest，只是求最小值
greatest与max的区别:
greatest是求列的最大值
max是求行的最大值
SQL> select greatest(1,2,3,4,5,6,7,8,9) from dual;
GREATEST(1,2,3,4,5,6,7,8,9)
---------------------------
SQL> select greatest(1,2,3,4,5,6,7,null,9) from dual;
GREATEST(1,2,3,4,5,6,7,NULL,9)
------------------------------
三.nvl,nvl2,coalesce函数
SQL> select nvl(null,'a') from dual;
NVL(NULL,'A')
-------------
SQL> select nvl2(null,'a','b') from dual;
NVL2(NULL,'A','B')
------------------
SQL> select coalesce('a',null,'b') from dual;
COALESCE('A',NULL,'B')
----------------------
四.replace,translate函数
replace 语法：REPLACE ( char , search_string [, replacement_string] )
translate 语法: TRANSLATE ( ‘char’ , ‘from_string’ , ‘to_string’ )
replace 会把search_string当做一个整体来进行替换
translate 会把from_string拆分成一个一个的字符，与to_string一个一个对应进行替换
translate比replace功能更强大
translate(‘123abc’,‘2dc’,‘4e’): 将会把2->4,d->e,c-’’
translate(‘123abc’,‘2’,‘4e’): 将会把2->4
SQL> SELECT REPLACE('acdd','cd','ef') FROM dual;
REPLACE('ACDD','CD','EF')
-------------------------
SQL> SELECT TRANSLATE('acdd','cd','ef') FROM dual;
TRANSLATE('ACDD','CD','EF')
---------------------------
SQL> select translate('123abc','2dc','4e') from dual;
TRANSLATE('123ABC','2DC','4E')
------------------------------
143ab
SQL> select translate('123abc','2','4e') from dual;
TRANSLATE('123ABC','2','4E')
----------------------------
143abc
五.ceil ,floor,round,trunc函数
ceil --向上取整
floor --像下取整
round --四舍五入
trunc --截断
SQL> select ceil(-1.2),ceil(1.4),ceil(1.6) from dual;
CEIL(-1.2) CEIL(1.4) CEIL(1.6)
---------- ---------- ----------
-1 2 2
SQL> select floor(-1.2),floor(1.4),floor(1.6) from dual;
FLOOR(-1.2) FLOOR(1.4) FLOOR(1.6)
----------- ---------- ----------
-2 1 1
SQL> select round(1.4),round(1.6),round(1.456,2) from dual;
ROUND(1.4) ROUND(1.6) ROUND(1.456,2)
---------- ---------- --------------
1 2 1.46
SQL> select trunc(1.4),trunc(1.6),trunc(1.456,2) from dual;
TRUNC(1.4) TRUNC(1.6) TRUNC(1.456,2)
---------- ---------- --------------
1 1 1.45
trunc函数在时间处理上还是比较常用的
--当年的一月一日
SQL> select trunc(sysdate,'YEAR') A1 FROM DUAL;
-----------
2020/1/1
--当月的第一天
SQL> select trunc(sysdate,'MONTH') A1 FROM DUAL;
-----------
2020/5/1
SQL> select trunc(sysdate,'MM') A1 FROM DUAL;
-----------
2020/5/1
--当天0时0分0秒
SQL> select trunc(sysdate,'DD') A1 FROM DUAL;
-----------
2020/5/2
SQL> select trunc(sysdate) A1 from DUAL;
-----------
2020/5/2
六.lpad,rpad函数
lpad(expr1,n,expr2) 从expr1中截取n个字符返回，如果长度不够就用expr2填充左边
rpad(expr1,n,expr2) 从expr1中截取n个字符返回，如果长度不够就用expr2填充右边
SQL> select lpad('Hello World',21,'ABC') a1 from dual;
---------------------
ABCABCABCAHello World
SQL> select lpad('Hello World',5,'ABC') a1 from dual;
-----
Hello
SQL> select rpad('Hello World',21,'ABC') a1 from dual;
---------------------
Hello WorldABCABCABCA
SQL> select rpad('Hello World',5,'ABC') a1 from dual;
-----
Hello
七.trim，ltrim，rtrim 函数
ltrim(ch,set) 将字符串ch左边的包含在set中的字符移除，如不指定set，就位空格，中间有其它间隔就停止截取
rtrim(ch,set) 将字符串ch右边的包含在set中的字符移除，如不指定set，就位空格，中间有其它间隔就停止截取
trim 默认去掉两端的空格，从左或右去掉第二个参数指定的字符，仅一个
--ltrim去除字符串左边的空格
--trim去除字符串左边和右边的空格
--rtrim去除字符串右边的空格
--要去除全部的，需要使用replace函数
SQL> select ltrim(' abc def '),trim(' abc def '),rtrim(' abc def ') from dual;
LTRIM('ABCDEF') TRIM('ABCDEF') RTRIM('ABCDEF')
--------------- -------------- ---------------
abc def abc def abc def
SQL> select ltrim('B_AHello','AB_') a1,ltrim('B_FAHello','AB_') a2 from dual;
A1 A2
----- -------
Hello FAHello
SQL> select trim('abcd' from 'abcdefghia') from dual;
select trim('abcd' from 'abcdefghia') from dual
ORA-30001: 截取集仅能有一个字符
SQL> select trim('a' from 'abcdefghia') from dual;
TRIM('A'FROM'ABCDEFGHIA')
-------------------------
bcdefghi
八.substr函数
substr(ch,position,length) 截取字符串
ch --字符串
position --开始位置
length --截取长度
SQL> select substr('Hello World',1,2) a1,substr('Hello World',2) a2,substr('Hello World',-1) a3 from dual;
A1 A2 A3
-- ---------- --
He ello World d
九.instr函数
instr共有4个参数
instr(string, substring, startposition, occurrence)
string --字符串
substring --需要匹配的字符串
startposition --查找的起始位置
occurrence --匹配的次数
SQL> select instr('oracleor','or', 1) position from dual;
POSITION
----------
SQL> select instr('oracleor','or', 2) position from dual;
POSITION
----------
SQL> select instr('oracleor','or', 1,2) position from dual;
POSITION