Oracle常用字符函数小结

Table of Contents

• 一.concat(),||函数
• 二.greatest,least函数
• 三.nvl,nvl2,coalesce函数
• 四.replace,translate函数
• 五.ceil ,floor,round,trunc函数
• 六.lpad,rpad函数
• 七.trim，ltrim，rtrim 函数
• 八.substr函数
• 九.instr函数

常用字符函数：

备注:测试数据库版本为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