TRIM的用法

TRIM函数用来对字符串进行剪裁操作，包括TRIM,LTRIM,RTRIM三个函数。虽然平常用的比较多，但还是有些用法没有细究过。

1.TRIM可以带语义型参数

TRIM(str1)，表示去掉两边空格
TRIM(leading str2 from str1)，去掉str1左边的str2，相当于LTRIM(str1,str2)
TRIM(trailing str2 from str1)，去掉str1右边的str2，相当于RTRIM(str1,str2)
TRIM(both str2 from str1)，去掉str1两边的str2。

SQL> select trim('   x   ') from dual;
TRIM('X')
---------
xSQL> select trim(leading '.' from '...x...') from dual;
TRIM(LEADING'.'FROM'...X...')
-----------------------------
x...SQL> select trim(trailing '.' from '...x...') from dual;
TRIM(TRAILING'.'FROM'...X...')
------------------------------
...xSQL> select trim(both '.' from '...x...') from dual;
TRIM(BOTH'.'FROM'...X...')
--------------------------
x

2.去掉两边单字符有两种方法

--不能直接用trim(str1,str2)
SQL> select trim('aaaxaaa','a') from dual;
select trim('aaaxaaa','a') from dual
ORA-00907: missing right parenthesis--方法1.ltrim(rtrim())或rtrim(ltrim())
SQL> select ltrim(rtrim('aaaxaaa','a'),'a') from dual;
LTRIM(RTRIM('AAAXAAA','A'),'A'
------------------------------
x--方法2.trim(both str2 from str1) 
SQL> select trim(both 'a' from 'aaaxaaa') from dual;
TRIM(BOTH'A'FROM'AAAXAAA')
--------------------------
x

3.去掉多个字符只能用ltrim或rtrim

SQL> select ltrim('abcxcab','abc') from dual;
LTRIM('ABCXCAB','ABC')
----------------------
xcabSQL> select rtrim('abcxcab','abc') from dual;
RTRIM('ABCXCAB','ABC')
----------------------
abcxSQL> select trim(both 'abc' from 'abcxcab') from dual;
select trim(both 'abc' from 'abcxcab') from dual
ORA-30001: trim set should have only one character

trim函数带单字符str2参数会报错ORA-00907: missing right parenthesis
带多字符语义参数会报错ORA-30001: trim set should have only one character。

而且，ltrim或rtrim不是完全匹配str2字符串，而是把str2中存在的字符全部剪裁掉，不论顺序，知道字符在str2中不存在。再看下面的例子会更清晰：

SQL> select rtrim('abcxcab','Abc') from dual;
RTRIM('ABCXCAB','ABC')
----------------------
abcxca

Step1.函数rtrim指明从右到左剪裁
Step2.str1的最右边字符’b’，存在于str2(‘Abc’)内，裁掉
Step3.str1右边第二个字符’a’，在str2中不存在，rtrim函数终止。