LTRIM、RTRIM、TRIM 功能说明

1. LTRIM

* syntax

• ltrim ( string-to-be-trimmed [, trimming-char-set ] );

SQL> select ltrim('******My name is Harvey.Wang******','*') from dual;


LTRIM('******MYNAMEISHARVEY.
----------------------------
My name is Harvey.Wang******


SQL> select ltrim('*$*$**My name is Harvey.Wang*$*$**','*') from dual;


LTRIM('*$*$**MYNAMEISHARVEY.WANG*
---------------------------------
$*$**My name is Harvey.Wang*$*$**


SQL> select ltrim('*$*$**My name is Harvey.Wang*$*$**','*$') from dual;


LTRIM('*$*$**MYNAMEISHARVEY.
----------------------------
My name is Harvey.Wang*$*$**

* Note

• ltrim 可以去掉多个字符（例如：‘abc’，代表如果遇到字符 a or b or c 都会被去掉）
• 去掉字符的顺序从左边第一个字符开始，直到遇到不在给定字符集的字符处停止

2. RTRIM

* syntax

• rtrim ( string-to-be-trimmed [, trimming-char-set ] );

SQL> select rtrim('*$*$**My name is Harvey.Wang*$*$**','*$') from dual


RTRIM('*$*$**MYNAMEISHARVEY.
----------------------------
*$*$**My name is Harvey.Wang


SQL> select rtrim('*$*$**My name is Harvey.Wang*$*$**','$') from dual;


RTRIM('*$*$**MYNAMEISHARVEY.WANG*$
----------------------------------
*$*$**My name is Harvey.Wang*$*$**


SQL> select rtrim('*$*$**My name is Harvey.Wang*$*$**','*') from dual;


RTRIM('*$*$**MYNAMEISHARVEY.WANG
--------------------------------
*$*$**My name is Harvey.Wang*$*$

* Note

• rtrim 可以去掉多个字符（例如：‘abc’，代表如果遇到字符 a or b or c 都会被去掉）
• 去掉字符的顺序从右边第一个字符开始，直到遇到不在给定字符集的字符处停止

3. TRIM

* syntax

• trim ( string-to-be-trimmed );
• trim ( trim-char from string-to-be-trimmed );
• trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed );

SQL> select trim('    My name is Harvey.Wang    ') from dual;


TRIM('MYNAMEISHARVEY.W
----------------------
My name is Harvey.Wang


SQL> select trim(both from '    My name is Harvey.Wang    ') from dual;


TRIM(BOTHFROM'MYNAMEIS
----------------------
My name is Harvey.Wang


SQL> select trim(both '*' from '******My name is Harvey.Wang******') from dual;


TRIM(BOTH'*'FROM'*****
----------------------
My name is Harvey.Wang

SQL> select trim(leading '*' from '******My name is Harvey.Wang******') from dual;


TRIM(LEADING'*'FROM'******MY
----------------------------
My name is Harvey.Wang******


SQL> select trim(trailing '*' from '******My name is Harvey.Wang******') from dual;


TRIM(TRAILING'*'FROM'******M
----------------------------
******My name is Harvey.Wang

SQL> select trim(trailing '*$' from '*$****My name is Harvey.Wang******') from dual;
select trim(trailing '*$' from '*$****My name is Harvey.Wang******') from dual
       *
第 1 行出现错误:
ORA-30001: 截取集仅能有一个字符

* Note

• trim 函数默认去掉字符串前后的‘ ’（空格）；也可以去掉指定的字符
• 通过添加 both、leading、trailing 决定去掉对应字符的位置
• trim 只能去掉一个字符（only one character）