TRIM, LTRIM and RTRIM are SQL functions used to filter unwanted characters from strings. By default they remove spaces, but a set of characters can be specifIEd for removal as well.
TRIM, LTRIM 、RTRIM是用於過濾一些不想要的字符的sql函數，默認去除空格，也可以去除指定的字符串。
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 if trim_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.
下面舉例說明：
1、trim(str)等價於trim(both from '字符串')
select trim(' 去除前後空格 ') from dual;
等價於
SQL> select trim(both from' 去除前後空格 ') from dual;
TRIM(BOTHFROM'去除前後空格')
----------------------------
去除前後空格
2、trim(leading from str)
SQL> select trim(leading from ' 用leading只去除前面空格，後面的空格不會去除 ')||'看看前面那句話後面的空格沒去掉吧' from dual;
TRIM(LEADINGFROM'用LEADING只去
-----------------------------------------------------------------------------
用leading只去除前面空格，後面的空格不會去除 看看前面那句話後面的空格沒去掉吧
3、trim(trailing from ' 字符串')
SQL> select 'Start'||trim(trailing from ' 用trailing只去除末尾的空格，字符串前面的空格不會去除 ')||'End' from dual;
'START'||TRIM(TRAILINGFROM'用T
--------------------------------------------------------------
Start 用trailing只去除末尾的空格，字符串前面的空格不會去除End
4、刪除字符串末尾的字符d
SQL> select 'Start='||trim(trailing 'd' from 'Hello World')||'=End' from dual;
'START='||TRIM(TRAILING'D'FROM
------------------------------
Start=Hello Worl=End
LTRIM
LTRIM()(char [,set])：該函數用於去掉字符串char左端所包含的set中的任何字符。Oracle從左端第一個字符開始掃描，逐一去掉在set中出現的字符，當遇到不是set中的字符時終止，然後返回剩余結果。
先看幾個實例：
SQL> select ltrim('109224323','109') from dual;
LTRIM('109224323','109')
------------------------
224323
這個的功能應該都知道的噢~~ 再來看一個：
SQL> select ltrim('10900094323','109') from dual;
LTRIM('10900094323','109')
---------------------------
4323
是不是有點迷糊了？按道理說應該是00094323的結果嘛~~ 再來看兩個對比的：
SQL> select ltrim('10900111000991110224323','109') from dual;
LTRIM('10900111000991110224323
------------------------------
224323
SQL> select ltrim('109200111000991110224323','109') from dual;
LTRIM('10920011100099111022432
------------------------------
200111000991110224323
是不是有這樣的疑問：為什麼第二個查詢語句多了一個2就沒被截了呢？
再來看一個：
SQL> select ltrim('902100111000991110224323','109') from dual;
LTRIM('90210011100099111022432
------------------------------
2100111000991110224323
我想大家都都會想：按道理說是截109的值，為什麼90也被截了？
總結：ltrim(x,y) 函數是按照y中的字符一個一個截掉x中的字符，並且是從左邊開始執行的，只要遇到y中有的字符, x中的字符都會被截掉, 直到在x的字符中遇到y中沒有的字符為止函數命令才結束 .
函數將109當成了三個字符以1，0，9在字符串開始直道出現不為1，0，9這三個字符中的任意一個開始截取;
可以看出，ltrim函數是從匹配函數開始之後出現在子串中任何字符都被屏蔽掉了;
ltrim('902100111000991110224323','109')從左邊開始截取，直到出現不符合1，0，9結束，即到2停止截取，不管2後面是否存在1，0，9照樣輸出，rtrim()同理
RTRIM 同LTRIM。。。
