Oracle之TRIM、LTRIM和RTRIM

最近发现一段对字符串进行trim 的SQL，发现LTRIM、RTRIM和TRIM 在ORACLE中的用法还是很强大的。

作用

三个函数功能都是对字符串进行前后去除一部分操作。但是又有点区别。先看下对比。

SELECT TRIM(BOTH '-' FROM '----FFF----ggggg----' ) AS trim_both,
TRIM( '-' FROM '----FFF----ggggg----' ) AS trim_default,
TRIM(LEADING '-' FROM '----FFF----ggggg----' ) AS trim_left, 
TRIM( TRAILING '-' FROM '-----FFF------ggggg------' ) AS trim_right,
TRIM('  ----FFF----ggggg----  ' ) AS trim_default_Empty, 
LTRIM('----FFF----ggggg----', '-F') AS ltrim_mult_char,
RTRIM('----FFF----ggggg----', '-g') AS rtrim_mult_char
FROM dual WHERE ROWNUM = 1;

上面利用三个函数对字符串进行处理，对比下不难发现一些区别。下面一个个进行理解。

TRIM

先看下文档介绍

trim( [ leading | trailing | both  [ trim_character ] FROM ]   string1 )

leading - remove trim_string from the front of string1.

trailing - remove trim_string from the end of string1.

both - remove trim_string from the front and end of string1.

If none of these are chosen (ie: leading, trailing, both), the trim function will remove trim_string from both the front and end of string1.

trim_character is the character that will be removed from string1. If this parameter is omitted, the trim function will remove all leading and trailing spaces from string1.

string1 is the string to trim.

这是 Oracle 的官方文档里面的函数介绍。
说白了，各个参数有以下作用：
leading  ： 去头
trailing ： 去尾
both     : 去头尾

trim_character ： 不空去该字符，缺省去除空格
string1 ： 待处理字符串

根据语法，不难发现会有以下的输出

BOTH

另外，如果该参数不指定时，默认也是 BOTH

SELECT TRIM(BOTH '-' FROM '----FFF----ggggg----' ) AS trim_both ,
TRIM( '-' FROM '----FFF----ggggg----' ) AS trim_default
FROM dual WHERE ROWNUM = 1;

LEADING

SELECT TRIM(LEADING '-' FROM '----FFF----ggggg----' ) AS trim_left 
FROM dual WHERE ROWNUM = 1;

TRAILING

SELECT TRIM( TRAILING '-' FROM '-----FFF------ggggg------' ) AS trim_right 
FROM dual WHERE ROWNUM = 1;

而当我们不输入要删除的字符（注意，这里我说的是字符）时，默认删除头尾的空格。

SELECT TRIM('  ----FFF----ggggg----  ' ) AS trim_default_Empty
FROM dual WHERE ROWNUM = 1;

上面没输入要trim 的单个字符参数时，函数只是把前后空格 trim 掉。为啥要单个字符?看个例子，

SELECT TRIM( TRAILING '--' FROM '-----FFF------ggggg------' ) AS trim_right 
FROM dual WHERE ROWNUM = 1;

这里报错，显然其参数必须是单个字符。错误信息如下：

ORA-30001: trim set should have only one character
30001. 00000 -  "trim set should have only one character"
*Cause:    Trim set contains more or less than 1 character. This is not
           allowed in TRIM function.

LTRIM

从名字就知道，对字符串左侧进行 trim。

SELECT LTRIM('----FFF----ggggg----', '-F') AS ltrim_mult_char
FROM dual WHERE ROWNUM = 1;

这里 LTRIM 将 '----FFF----ggggg----' 的头部的 '-' 和 'F' 都去掉了，最后变成了 'ggggg----'。显然 LTRIM 对发现的任意字符 '-' 或字符 'F' 均做删除操作。即或的关系。

RTRIM

同样的，RTRIM 功能类似，只是从尾部开始删除而已。

SELECT RTRIM('----FFF----ggggg----', '-g') AS rtrim_mult_char
FROM dual WHERE ROWNUM = 1;

从结果看，同样的对 '-' 和 'F' 进行删除。也是或的关系。