oracle中LTRIM、RTRIM的用法

LTRIM（x,y）的英文解释是这样的：

LTRIM removes from the left end of char all of the characters contained in set. 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 quotation marks. Oracle Database begins scanning char from its first character and removes all characters that appear in set until 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, NVARCHAR2 if char is a national character datatype, and a LOB if char is a LOB datatype.
也就是按照y中的字符一个一个截掉x中的字符，并且是从左边开始执行的，只要遇到y中有的字符, x中的字符都会被截掉, 直到在x的字符中遇到y中没有的字符为止函数命令才结束 . 举个例子来说：

 

ltrim('abcdab','ab') 结果并不是按‘ab’字符串来截断'abcdba'，而是分别按照字母a和b分别去找，分别按不是a和b的字符处开始截取。ltrim函数是从匹配函数开始之后出现在子串中任何字符都被屏蔽掉了;结果将是'cdab'。

 

如此一来，ltrim有一个妙用就是判断是否为数字格式，

select t.price from price_table ltrim(t.price,'0123456789.') is null

判断是否为整型：

select t.price from price_table ltrim(t.price,'0123456789') is null

 

oracle中还有RTRIM函数：

 

RTRIM(<c1>,<c2> ) takes two arguments, where c1 and c2 are character

strings. This function returns c1 without any trailing characters that appear

in c2. If no c2 characters are trailing characters in c1, then c1 is returned

unchanged. c2 defaults to a single space.

 

与ltrim不同的是查找的顺序不同，从字符串的右侧开始查找。

 

SELECT RTRIM('Mississippi','ip') test1,RTRIM('Rpadded ') test2 ,RTRIM('Mississippi','sip') test3 FROM

 

dual;
结果为：
test1 test3 test3
Mississ Rpadded M
test1的实现
首先从字符串'Mississippi'右边查找'ip'中的任意字符，此例为'i','p',直到'Mississippi'右边不为'i'和'p'字符为止，所以结果应该是Mississ