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')
---------
x
SQL> select trim(leading '.' from '...x...') from dual;
TRIM(LEADING'.'FROM'...X...')
-----------------------------
x...
SQL> select trim(trailing '.' from '...x...') from dual;
TRIM(TRAILING'.'FROM'...X...')
------------------------------
...x
SQL> 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')
----------------------
xcab
SQL> select rtrim('abcxcab','abc') from dual;
RTRIM('ABCXCAB','ABC')
----------------------
abcx
SQL> 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函数终止。