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(),||函数

concat 仅支持2个字符串的连接，使用得不多
|| 支持多个字符串的连接

通过字符串连接函数||和select语句结合，可以批量生成sql语句，提升效率

SQL> select concat('a','bc') from dual;
 
CONCAT('A','BC')
----------------
abc
 
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)
---------------------------
                          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')
-------------
a
 
SQL> select nvl2(null,'a','b') from dual;
 
NVL2(NULL,'A','B')
------------------
b
SQL> select coalesce('a',null,'b') from dual;
 
COALESCE('A',NULL,'B')
----------------------
a

四.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')
-------------------------
aefd
 
SQL> SELECT TRANSLATE('acdd','cd','ef') FROM dual;
 
TRANSLATE('ACDD','CD','EF')
---------------------------
aeff
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> 
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;
 
A1
-----------
2020/1/1
--当月的第一天 
SQL> select trunc(sysdate,'MONTH') A1 FROM DUAL;
 
A1
-----------
2020/5/1
 
SQL> select trunc(sysdate,'MM') A1 FROM DUAL;
 
A1
-----------
2020/5/1
--当天0时0分0秒
SQL> select trunc(sysdate,'DD') A1 FROM DUAL;
 
A1
-----------
2020/5/2
 
SQL> select trunc(sysdate) A1 from DUAL;
 
A1
-----------
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;
 
A1
---------------------
ABCABCABCAHello World
 
SQL> select lpad('Hello World',5,'ABC') a1 from dual;
 
A1
-----
Hello
 
SQL> select rpad('Hello World',21,'ABC') a1 from dual;
 
A1
---------------------
Hello WorldABCABCABCA
 
SQL> select rpad('Hello World',5,'ABC') a1 from dual;
 
A1
-----
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
----------
         1
 
SQL> select instr('oracleor','or', 2) position from dual;
 
  POSITION
----------
         7
 
SQL> 
SQL> 
SQL> select instr('oracleor','or', 1,2) position from dual;
 
  POSITION
----------
         7