A detailed description of the trim () function in Oracle

1. Take a look at the complete syntax description of the Oracle 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/... 0/img_text/trim.htm
From this syntax definition we can see that a small Oracle 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 (' 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 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 ' Dweye ') "trim e.g." from dual;

TRIM e.g
--------
Dweye

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 ' dweye ') "trim e.g." from dual;

TRIM e.g.
------------
Dweye

[Email protected]> Select trim (Leading from ' Dweye ') "trim e.g." from dual;

TRIM e.g.
------------
Dweye

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 ' 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]ww.dweye.net> 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

A detailed description of the trim () function in Oracle