Oracle常用字符函数小结

文章目录

• 一.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\')----------------abcSQL> select concat(\'a\',\'bc\',\'def\') from dual;select concat(\'a\',\'bc\',\'def\') from dualORA-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)---------------------------9SQL> 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\')-------------aSQL> select nvl2(null,\'a\',\'b\') from dual;NVL2(NULL,\'A\',\'B\')------------------bSQL> 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\')-------------------------aefdSQL> SELECT TRANSLATE(\'acdd\',\'cd\',\'ef\') FROM dual;TRANSLATE(\'ACDD\',\'CD\',\'EF\')---------------------------aeffSQL> select translate(\'123abc\',\'2dc\',\'4e\') from dual;TRANSLATE(\'123ABC\',\'2DC\',\'4E\')------------------------------143abSQL> 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          2SQL> select floor(-1.2),floor(1.4),floor(1.6) from dual;FLOOR(-1.2) FLOOR(1.4) FLOOR(1.6)----------- ---------- -----------2          1          1SQL> 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.46SQL>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/1SQL> 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/2SQL> 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 WorldSQL> select lpad(\'Hello World\',5,\'ABC\') a1 from dual;A1-----HelloSQL> select rpad(\'Hello World\',21,\'ABC\') a1 from dual;A1---------------------Hello WorldABCABCABCASQL> 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 defSQL> select ltrim(\'B_AHello\',\'AB_\') a1,ltrim(\'B_FAHello\',\'AB_\') a2 from dual;A1    A2----- -------Hello FAHelloSQL> select trim(\'abcd\' from \'abcdefghia\') from dual;select trim(\'abcd\' from \'abcdefghia\') from dualORA-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----------1SQL> select instr(\'oracleor\',\'or\', 2) position from dual;POSITION----------7SQL>SQL>SQL> select instr(\'oracleor\',\'or\', 1,2) position from dual;POSITION----------7