An explanation of Oracle Trim function usage

In an Oracle database, trim is generally used to delete spaces on both sides of a string. In fact, trim can also be used to delete specified characters on both sides of a string. And trim specifies that the deleted string can only be a single character
The Oracle Trim function is a common function, and the syntax for the Oracle trim function is described in detail below, hopefully allowing you to gain a deeper understanding of the Oracle trim function.

If you refer to the Oracle Trim function, 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 features.
However, the Oracle trim function actually has the ability to delete "any specified" character, which is not an ox. Let's take a journey of experience.
1. Take a look at the complete syntax description for the Oracle Trim function
Trim ([{{leading | TRAILING | BOTH}
[Trim_character]
| Trim_character
}
from
]
Trim_source
)
The above syntax is quoted from Oracle 10GR2 Official document: http://download.oracle.com/docs/... 0/img_text/trim.htm
Single from this syntax definition, we can see that the small Oracle trim function contains more customizable features. One by one display, for reference.
2. The simplest usage start
does not use any parameters:
[email protected]> Select Trim (' Dweye ') "trim e.g." from dual;
Trim e.g
--------
Dweye
This is also the most common way to use the default parameters, which by default trim removes 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 ' dweye ') "trim e.g." from dual;
TRIM e.g
--------
Dweye
"BOTH" parameter means that the content specified before and after the string is removed (whitespace is deleted 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 ' dweye ') "trim e.g." from dual;
Trim e.g.
------------
Dweye
[email protected]> Select trim (Leading from ' Dweye ') "trim e.g." fro M dual;
TRIM e.g.
------------
Dweye
, you can use the "TRAILING" parameter to complete the deletion of trailing spaces in the string, while 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 ' Xxxxdweyexxxx ', the "trim_character" parameter comes in handy.
[Email protected]> Select trim (' x ' from ' xxxxdweyexxxx ') "trim e.g." from dual;
TRIM e.g
--------
Dweye
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 ' xxxxdweyexxxx ') "trim e.g." from dual;
TRIM e.g
--------
Dweye
[Email protected]> Select trim (trailing ' x ' from ' xxxxdweyexxxx ') "trim e.g." from dual;
TRIM e.g.
------------
Xxxxdweye
[Email protected]> Select trim (Leading ' x ' from ' xxxxdweyexxxx ') "trim e.g." from dual;
TRIM e.g.
------------
Dweyexxxx
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 ' xyxxdweyexyyx ') "trim e.g." from dual;
Select trim (Leading ' xy ' from ' xyxxdweyexyyx ') "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 "Dweye" 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 (' Xyxxdweyexyyx ', ' xy ') "e.g." from dual;
e.g.
------------
Xyxxdweye
2) Use LTrim
[Email protected]> Select LTrim (' Xyxxdweyexyyx ', ' xy ') "e.g." from dual;
e.g.
------------
Dweyexyyx
3) combined use of RTrim and LTRIM functions to achieve our goal
[Email protected]> Select LTrim (RTrim (' Xyxxdweyexyyx ', ' xy '), ' xy ') "e.g." from dual;
e.g.
--------
Dweye
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 as a bucket of their true functions.
The following is a supplement to other netizens:
such as trim (' character 1 ' from ' String 2 '), the character 1 can only be a single character.
1. Trim () Remove the spaces on either side of the string.
2. LTrim () Remove the space to the left of the string.
3. RTrim () Delete the space to the right of the string.
4. Trim (' character 1 ' from ' String 2 ') starts from both sides of the character 2 string, removing the specified character 1.
5. Trim ([leading | trailing | both] Trim_char from string) removes the specified character Trim_char from the strings.
Leading: Deletes from the beginning of the string.
Trailing: Deletes from the end of the string.
Borth: Removed from both sides of the string.
6. Tim () can only delete half-width spaces.
For example:
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 ';
Trim functions in Oracle

The TRIM function in Oracle is used to delete the given string or the specified character in the head or tail of a given number.
The TRIM function has the following form

Trim ([leading/trailing/both][match string or numeric][from][string or numeric value to be processed])

If you indicate that leading represents a string that matches the header from the deletion, if the trailing indicates a string that matches from the end of the deletion, if the both is indicated, or if no location is specified, both ends are deleted

If you do not indicate that any matching string or numeric value is considered a space, delete the preceding or trailing space.

The type returned by the Trim function is VARCHAR2

Here are some examples:

Cut the number 7500 behind the two X 0

Sql> Select Trim (0 from 7500) from dual;

TRIM

—-

75

The following example truncates the ' mid-autumn August ' before and after the two ' middle ' characters

Sql> Select Trim (' Medium ' from ' Mid-autumn August ') as poetry from dual;

Poems

——————

Autumn August

The following example truncates a ' half ' word in front of ' midnight Two and a half '

Sql> Select trim (leading ' half ' from ' midnight Two more Half ') as poems from dual;

Poems

————————

Night two more half