TRIM & LTRIM & RTRIM

TRIM & LTRIM & RTRIM 的用法。

真的和你想得一样的简单吗？

[@more@]

LTRIM(char [, set ])

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 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 and is in the same character set as char.
Examples

The following example trims all of the left-most x's and y's from a string:

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"
   FROM DUAL;

LTRIM example
------------
XxyLAST WORD

RTRIM(char [, set ])

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 and is in the same character set as char.

Examples

The following example trims all the right-most occurrences of period, slash, and equal sign from a string:

SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" FROM DUAL;
 
RTRIM exam
----------
BROWNING:

TRIM([ { { LEADING | TRAILING | BOTH }
         [ trim_character ]
       | trim_character
       }
FROM 
     ]
     trim_source
    )

Purpose

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 and is in the same character set as trim_source.

Examples

This example trims leading zeroes from the hire date of the employees in the hr schema:

SELECT employee_id,
      TO_CHAR(TRIM(LEADING 0 FROM hire_date))
      FROM employees
      WHERE department_id = 60;

EMPLOYEE_ID TO_CHAR(T
----------- ---------
        103 3-JAN-90
        104 21-MAY-91
        105 25-JUN-97
        106 5-FEB-98
        107 7-FEB-99

LTRIM会把set中出现的字符全部删除，直到有不再set中出现的字符后停止。

RTRIM和LTRIM一样，不过是从右边开始。

TRIM可以设置从左或从右或者从2边，但是trim的截取集只能是单个字符。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133835/viewspace-930312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133835/viewspace-930312/