LTrim, RTrim, and trim in Oracle

LTRIM, RTrim, and trim usage in Oracle:
1, LTRIM (C1,C2)
where C1 and C2 can be strings, for example C1 is ' Miss Liu ', C2 ' MISL ' and so on. This is the first place that is not the same as SQL Server. If you remember good, SQL Server's LTrim has only one parameter, which removes the whitespace from the left of the string. Oracle's LTrim is to ensure that the first character of C1 cannot appear in the C2 string.

Sql> Select LTRIM (' Miss Liu ', ' Liu ') Result from dual;

RESULT
--------
Miss Liu

Sql> Select LTRIM (' Miss Liu ', ' M is ') the result from dual;

Res
---
Liu

From the above we can see the role of LTrim. But if the second string is not entered, then the LTrim function is the same as in SQL Server, removing the left space.

Sql> Select LTrim (' Miss Liu ') result from dual;

RESULT
----------
Miss Liu

Sql> Select Length (' Miss Liu ') len1, Length (LTrim (' Miss Liu ')) Lentrim from dual;

LEN1 Lentrim
----------   ----------
12 10

It can be seen from the above that Oracle's LTrim function should be more powerful and able to operate on the leader character.

2, RTrim function and ltrim the same, but RTrim modified to right-to-left, this way is to remove the specific characters after the guide.

3, the function of trim is described as follows:

In Oracle/plsql, the trim function removes any specified characters either from the beginning or the ending of a string.

The syntax for the TRIM function is:

Trim ([leading | trailing | both [Trim_character]] string1)

Leading-remove trim_string from the front of string1.

Trailing-remove trim_string from the end of string1.

Both-remove trim_string from the front and end of string1.

If None of these is chosen (ie:leading, trailing, both), the TRIM function would remove trim_string from both the front a nd end of string1.

Trim_character is the character, that would be, removed from string1. If This parameter is omitted, the trim function would remove all leading and trailing spaces from string1.

String1 is the string to trim.

Trim (' tech ') would return ' tech '
Trim (' from ' tech ') would return ' tech '
Trim (Leading ' 0 ' from ' 000123 ') would return ' 123 '
Trim (trailing ' 1 ' from ' Tech1 ') would return ' Tech '
Trim (both ' 1 ' from ' 123tech111 ') would return ' 23Tech

If the trim function is mentioned, the simplest function is to use it to remove the line at the beginning and end of the string, which is one of the most frequently used functions.
However, the trim function actually has the function of removing the "arbitrarily specified" character, which is not an ox. Let's take a journey of experience.

1. First look at the complete syntax description of the TRIM function
TRIM ([{{leading | TRAILING | BOTH}
[Trim_character]
| Trim_character
}
From
]
Trim_source
)

The above syntax is quoted from Oracle 10GR2 official documentation: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/img_text/trim.htm
From this syntax definition we can see that a small trim function implies more customizable functionality. One by one display, for reference.

2. The simplest usage begins
Do not use any parameters:
[email protected]> select trim (' Secooler ') "trim e.g." from dual;

TRIM e.g
--------
Secooler

This is also the most common way to use the default parameters, which by default trim will delete the spaces that appear before and after the string.

3. In fact, the first common method is equivalent to the following with the "BOTH" parameter of the wording
[email protected]> select Trim (both from ' Secooler ') "trim e.g." from dual;

TRIM e.g
--------
Secooler

The "BOTH" parameter indicates that the content specified before and after the string is removed (whitespace is removed by default).

4. Now that we've tried the both parameter, we'll look at the "TRAILING" and "leading" parameter effects.
[email protected]> select trim (Trailing from ' Secooler ') "trim e.g." from dual;

TRIM e.g.
------------
Secooler

[email protected]> select trim (Leading from ' Secooler ') "trim e.g." from dual;

TRIM e.g.
------------
Secooler

Visible, the "TRAILING" parameter can be used to complete the deletion of string trailing spaces, and the "leading" parameter is the opposite, complete the deletion of the string head space.
That is, you can use the "TRAILING" and "leading" parameters to specify where to delete spaces.

5. "Trim_character" parameter
This parameter changes the default behavior of "delete space".
If you want to delete the "X" that appears before and after the string ' Xxxxsecoolerxxxx ', the "trim_character" parameter comes in handy.
[email protected]> select Trim (' x ' from ' xxxxsecoolerxxxx ') "trim e.g." from dual;

TRIM e.g
--------
Secooler

The three parameters with "BOTH", "TRAILING", and "leading" have the following effects, similar to the previous demo. Look at the results, do not repeat.
[email protected]> select trim (both ' x ' from ' xxxxsecoolerxxxx ') "trim e.g." from dual;

TRIM e.g
--------
Secooler

[email protected]> select trim (Trailing ' x ' from ' xxxxsecoolerxxxx ') "trim e.g." from dual;

TRIM e.g.
------------
Xxxxsecooler

[email protected]> select trim (Leading ' x ' from ' xxxxsecoolerxxxx ') "trim e.g." from dual;

TRIM e.g.
------------
Secoolerxxxx

6. Places to be aware of
The "Trim_character" parameter here is only allowed to contain one character and does not support multiple characters.
The error message is as follows:
[email protected]> select trim (Leading ' xy ' from ' xyxxsecoolerxyyx ') "trim e.g." from dual;
Select trim (Leading ' xy ' from ' xyxxsecoolerxyyx ') "trim e.g." from dual
*
ERROR at line 1:
Ora-30001:trim set should has only one character

Since trim does not satisfy our requirement to delete only the "Secooler" string, is there any other means? Of course has. We use RTrim and LTrim "serial punches" to accomplish this task.
1) Use RTrim
[email protected]> select RTrim (' Xyxxsecoolerxyyx ', ' xy ') "e.g." from dual;

e.g.
------------
Xyxxsecooler

2) Use LTrim
[email protected]> select LTrim (' Xyxxsecoolerxyyx ', ' xy ') "e.g." from dual;

e.g.
------------
Secoolerxyyx

3) combined use of RTrim and LTRIM functions to achieve our goal
[email protected]> select LTrim (RTrim (' Xyxxsecoolerxyyx ', ' xy '), ' xy ') "e.g." from dual;

e.g.
--------
Secooler

Considerations when using the RTrim and LTrim functions: "XY" does not represent the entire "XY" string to match, but instead discovers any character "X" or the character "Y" to do the delete operation.

7. Summary
While feeling the benefits of Oracle functions, it is recommended to look back at each of the usual functions, perhaps after the attempt you will find: Oh, it turns out that these methods are often used only by the bucket of their true functions.

Transferred from: http://www.2cto.com/database/201208/147087.html

LTrim, RTrim, and trim in Oracle