一、字符类：

1)、lower：字母转小写

SQL> select lower('AbCd') from dual;

LOWE
----
abcd

2)、upper：字母转大写

SQL> select upper('AbCd') from dual;

UPPE
----
ABCD

3)、initcap：首字母转大写

SQL> select initcap('abc') from dual;

INI
---
Abc

4)、substr：从下标开始截取字符串，下标从1开始

SQL> select substr('abcd', 2) from dual;

SUB
---
bcd

SQL> select substr('abcd', 3) from dual;

SU
--
cd

5)、length：取字符串字符长度

SQL> select length('abc你好') from dual;

LENGTH('ABC你好')
-----------------
                5

SQL> select length('abc') from dual;

LENGTH('ABC')
-------------
            3

6)、lengthb：取字符串字节长度

SQL> select lengthb('abc') from dual;

LENGTHB('ABC')
--------------
             3

SQL> select lengthb('abc你好') from dual;

LENGTHB('ABC你好')
------------------
                 7

7)、instr：查找子串在母串中的位置

SQL> select instr('abcdef', 'def') from dual;

INSTR('ABCDEF','DEF')
---------------------
                    4

SQL> select instr('abcdef', 'ddd') from dual;

INSTR('ABCDEF','DDD')
---------------------
                    0

8)、lpad：将字符串从左填充字符

SQL> select lpad('132', 10) from dual;

LPAD('132'
----------
       132

SQL> select lpad('132', 10, '0') from dual;

LPAD('132'
----------
0000000132

SQL> select lpad('abc', 7,'123') from dual;

LPAD('A
-------
1231abc

9)、rpad：将字符串从右填充字符

SQL> select rpad('132', 10, '0') from dual;

RPAD('132'
----------
1320000000

10)、trim：删除字符串中指定字符

SQL> select trim('1' from '1231') from dual;

TR
--
23

SQL> select trim('12' from '1231') from dual;
select trim('12' from '1231') from dual
       *
第 1 行出现错误:
ORA-30001: 截取集仅能有一个字符

11)、replace：替换字符串中指定子串

SQL> select replace('wabdsahjhrtefcde', 'a', '你好') from dual;

REPLACE('WABDSAHJHRTEF
----------------------
w你好bds你好hjhrtefcde

SQL> select replace('wabdsahjhrtefcde', 'dsa', '你好') from dual;

REPLACE('WABDSAHJ
-----------------
wab你好hjhrtefcde

12)、concat：连接字符串

SQL> select concat('123456', '789') from dual;

CONCAT('1
---------
123456789

SQL> select concat(concat('123456', '789'),'abc') from dual;

CONCAT(CONCA
------------
123456789abc

SQL> select 'abc' || 'def' || '123' from dual;

'ABC'||'D
---------
abcdef123

二、数字类：

1)、round：四舍五入

SQL> select round(11.1111) from dual;

ROUND(11.1111)
--------------
            11

SQL> select round(11.1111, 2) from dual;

ROUND(11.1111,2)
----------------
           11.11

SQL> select round(11.1111, 99) from dual;

ROUND(11.1111,99)
-----------------
          11.1111

SQL> select round(11.1111, -1) from dual;

ROUND(11.1111,-1)
-----------------
               10

SQL> select round(11.1111, -2) from dual;

ROUND(11.1111,-2)
-----------------
                0

SQL> select round(50, -2) from dual;

ROUND(50,-2)
------------
         100

2)、trunc：取整

SQL> select trunc(11.115, 2) from dual;

TRUNC(11.115,2)
---------------
          11.11

SQL> select trunc(11.119, 2) from dual;

TRUNC(11.119,2)
---------------
          11.11

SQL> select trunc(11.111, -1) from dual;

TRUNC(11.111,-1)
----------------
              10

SQL> select trunc(19, -1) from dual;

TRUNC(19,-1)
------------
          10

3)、mod：求余

SQL> select mod(10, 3) from dual;

 MOD(10,3)
----------
         1

SQL> select mod(10, 4) from dual;

 MOD(10,4)
----------
         2

SQL> select mod(10, 5) from dual;

 MOD(10,5)
----------
         0

4)、to_char：格式化成字符串

有点多，在百度找到一个比较好的教程，点我点我！