Trim,ltrim,rtrim function usage in Oracle
Trim,ltrim,rtrim function usage in Oracle
There are two kinds of functions in this function:
the first, that is, everyone is more familiar with the removal of space.
Example:
--trim to remove spaces before and after a specified character
sql> SELECT TRIM (' DD DF ') from dual;
TRIM (' Dddf ')
------------
DD DF
--ltrim Remove the front space of the specified character
sql> SELECT LTRIM (' DD DF ') from dual;
LTRIM (' Dddf ')
-------------
DD DF
--rtrim remove space after the specified character
sql> SELECT RTRIM (' DD DF ') from dual;
RTRIM (' Dddf ')
-------------
DD DF
The second, removes the specified character. Trim can only remove single characters, while LTrim and RTrim can remove multiple characters.
Trim to remove a character's wording:
--Represents the string string2 to remove the character string1 of the front | back | front side (leading|trailing|both), the default removal method is both
SELECT TRIM (Leading|trailing|both string1 from string2) from dual;
Example:
Sql> SELECT Trim (leading ' d ' from ' Dfssa ') from dual;
TRIM (Leading ' D ' from ' Dfssa ')
---------------------------
Fssa
Sql> SELECT Trim (both ' 1 ' from ' 123sfd111 ') from dual;
TRIM (BOTH ' 1 ' from ' 123sfd111 ')
----------------------------
23sfd
Sql> SELECT Trim (trailing ' 2 ' from ' 213dsq12 ') from dual;
TRIM (TRAILING ' 2 ' from ' 213dsq12 '
------------------------------
213dsq1
Note: The trim removal character can only be a single character, such as the following, the character to be removed characters as a character set error
Sql> SELECT trim (trailing ' from ' 123dsq12 ') from dual;
SELECT trim (trailing ' from ' 123dsq12 ') from dual
ORA-30001: The Intercept set can have only one character
LTrim and RTrim to remove characters:
--Indicates that the string string1 removes the previous match to the string2 character set, and if no match ends the return
SELECT LTrim (string1,string2) from dual;
--rtrim is similar to LTrim, except that it removes the matching character from the right side.
SELECT RTrim (string1,string2) from dual;
Example:
--as follows, because the first letter from the right side is B does not match the ' main ' character, so the returned result is still ' aaaaminb '
sql> SELECT RTrim (' aaaaminb ', ' main ') from dual;
RTRIM (' aaaaminb ', ' MAIN ')
------------------------
Aaaaminb
--The returned result is empty as follows
sql> SELECT RTrim (' aaaaminb ', ' mainb ') from dual;
RTRIM (' aaaaminb ', ' mainb ')
-------------------------
sql> SELECT ltrim (' ccbcminb ', ' CB ') from dual;
LTRIM (' ccbcminb ', ' CB ')
----------------------
Minb
Trim,ltrim,rtrim function usage in Oracle