Trim函数的用法

  Oracle Trim函数最简单的功能就是去除字符串的行首和行尾的空格。其实Oracle Trim函数还具有删除“任意指定”字符的功能，有时可极大方便我们获取需要的数据。

  Trim完整语法描述：

  TRIM([ { { LEADING | TRAILING | BOTH }

         [ trim_character ]
       | trim_character
       }
       FROM 
     ]
     trim_source
    )

TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotes.
 
 
If you specify LEADING, then Oracle Database removes any leading characters equal to trim_character.
If you specify TRAILING, then Oracle removes any trailing characters equal to trim_character.
If you specify BOTH or none of the three, then Oracle removes leading and trailing characters equal to trim_character.
If you do not specify trim_character, then the default value is a blank space.
If you specify only trim_source, then Oracle removes leading and trailing blank spaces.
The function returns a value with datatype VARCHAR2. The maximum length of the value is the length of trim_source.
If either trim_source or trim_character is null, then the TRIM function returns null.
Both trim_character and trim_source can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype iftrim_source is a character datatype and a LOB if trim_source is a LOB datatype. The return string is in the same character set as trim_source.

一般用法(不使用参数)：

SQL> select trim(' DISVOID ') "e.g" from dual; --使用默认参数，默认情况下Trim会同时字符串前后出现的空格
 
e.g
-------
DISVOID

SQL> select trim( both from ' DISVOID ') "e.g" from dual;  --删除字符串前后空格
 
e.g
-------
DISVOID
 
SQL> select trim( trailing from ' DISVOID ') "e.g" from dual;  --删除字符串行尾空格
 
e.g
--------
 DISVOID
 
SQL> select trim( leading from ' DISVOID ') "e.g" from dual;  --删除字符串行首空格

e.g
--------
DISVOID

SQL> select trim( 'x' from 'xxDISVOIDxx') "e.g" from dual;  --与both相同
 
e.g
-------
DISVOID
 
SQL> select trim(both 'x' from 'xxDISVOIDxx') "e.g" from dual;  --截断前后出现的x
 
e.g
-------
DISVOID

SQL> select trim(trailing 'x' from 'xxDISVOIDxx') "e.g" from dual;  --删除字符串行尾字符
 
e.g
---------
xxDISVOID
 
SQL> select trim(leading 'x' from 'xxDISVOIDxx') "e.g" from dual;  --删除字符串行首字符
 
e.g
---------
DISVOIDxx

注意：trim_character 参数只允许包含一个字符，不支持多字符

SQL> select trim( 'xy' from 'xyxxDISVOIDxyyx') "e.g" from dual; 
select trim( 'xy' from 'xyxxDISVOIDxyyx') "e.g" from dual 
ORA-30001: 截取集仅能有一个字符
此时可以通过RTRIM和LTRIM
LTRIM
LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, it defaults to a single blank. If char is a character literal, then you must enclose it in single quotes. Oracle Database begins scanning char from its first character and removes all characters that appear in setuntil reaching a character not in set and then returns the result.
Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if char is a character datatype and a LOB if char is a LOB datatype.

RTRIM
RTRIM removes from the right end of char all of the characters that appear in set. This function is useful for formatting the output of a query.
If you do not specify set, then it defaults to a single blank. If char is a character literal, then you must enclose it in single quotes. RTRIM works similarly to LTRIM.
Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if char is a character datatype and a LOB if char is a LOB datatype.
SQL> select ltrim('xx11xx311xdx','1x3') from dual;   --截断左边的1|x|3字符  对任意1，x，3字符都做删除
LTRIM('XX11XX311XDX','1X3')
---------------------------
dx 
SQL> select rtrim('xx11xx311xdx','1x3') from dual;  --截断右边的1|x|3字符
RTRIM('XX11XX311XDX','1X3')
---------------------------
xx11xx311xd

因此上面的功能可以通过RTRIM和LTRIM组合完成
SQL> select rtrim('xyxxDISVOIDxyyx' , 'xy' ) "e.g" from dual; 
e.g
-----------
xyxxDISVOID
SQL> select ltrim('xyxxDISVOIDxyyx' , 'xy' )  "e.g" from dual; 
 e.g 
----------- 
DISVOIDxyyx
SQL> select ltrim(rtrim('xyxxDISVOIDxyyx' , 'xy' ) , 'xy' )  "e.g"  from dual;
 e.g 
------- 
DISVOID