Introduction to the use of trim functions in Oracle _oracle

The Oracle trim function is a very common function, and the syntax of the Oracle trim function is described in detail below, hoping to give you a deeper understanding of the Oracle trim function.

If you mention the Oracle Trim function, the simplest function is to use it to remove the beginning of the string and the space at the end of the line, which is the most frequently used.
However, the Oracle trim function actually has the ability to delete the "arbitrarily specified" character, which is not a cow. Let's go on a journey of experience.

1. See the full syntax description of the Oracle Trim function first

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
As we can see from this syntax definition, the small Oracle trim function contains more customizable functionality. One by one display, for reference.

2. The simplest use of the beginning
do not use any parameters:
Gloria@www.dweye.net> Select Trim (' Dweye ') "trim e.g." from dual;

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

This is also the most common use method, using the default parameters, and by default trim will also remove the spaces that appear before and after the string.

3. In fact, the first commonly used method is equivalent to the following with "BOTH" parameters of the wording
gloria@www.dweye.net> 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 deleted by default).

4. Now that we have tried the both parameter, let's take a look at the "trailing" and "leading" parameter effects

gloria@www.dweye.net> Select trim (trailing from ' dweye ') "trim e.g." from dual;

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

Gloria@www.dweye.net> Select trim (Leading from ' Dweye ') "trim e.g." from dual;

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

Visible, you can use the "trailing" parameter to complete the deletion of the trailing spaces of the string, and the "leading" argument completes the deletion of the space in the string's head.
That is, you can use the "trailing" and "leading" parameters to specify the deletion location of spaces.

5. "Trim_character" parameters

This parameter changes the default behavior of "remove spaces".
If you want to remove the "X" that appears before and after the string ' Xxxxdweyexxxx ', the "trim_character" argument comes in handy.
Gloria@www.dweye.net> Select Trim (' x ' xxxxdweyexxxx ') "trim e.g." from dual;

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

Working with the "BOTH", "trailing" and "leading" three parameters are as follows, similar to previous demos. See the results, do not repeat.
Gloria@www.dweye.net> Select Trim (both ' x ' from ' xxxxdweyexxxx ') "trim e.g." from dual;

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

Gloria@www.dweye.net> Select trim (trailing ' x ' from ' xxxxdweyexxxx ') "trim e.g." from dual;

TRIM e.g.
------------
Xxxxdweye

Gloria@www.dweye.net> Select trim (Leading ' x ' from ' xxxxdweyexxxx ') "trim e.g." from dual;

TRIM e.g.
------------
Dweyexxxx

6. Where attention is required

the "trim_character" argument here allows only one character, not multiple characters.
The error message is as follows:
Gloria@www.dweye.net> Select trim (Leading ' xy ' ' xyxxdweyexyyx ') "trim e.g." from dual;
Select trim (Leading ' xy ' xyxxdweyexyyx ') "trim e.g." from dual
*
ERROR at line 1:
Ora-30001:trim set should have only one character

Since the trim does not satisfy our requirement to delete the "Dweye" string, is there any other means? Of course have. We use RTrim and LTrim "serial punches" to accomplish this task.
1) using RTrim
Gloria@www.dweye.net> Select RTrim (' Xyxxdweyexyyx ', ' xy ') "e.g." from dual;

e.g.
------------
Xyxxdweye

2) Using LTrim
Gloria@www.dweye.net> Select LTrim (' Xyxxdweyexyyx ', ' xy ') "e.g." from dual;

e.g.
------------
Dweyexyyx

3) Joint use of RTRIM and LTRIM functions to achieve our goal
Gloria@www.dweye.net> 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 an entire "xy" string match, but it finds that any character "X" or character "Y" is deleted.

7. Summary

While feeling the convenience of Oracle functions, it is recommended to explore every common function, perhaps after you try to find: Oh, we often use these methods is only its true function of the bucket.

This is supplemented by other netizens:

such as trim (' character 1 ' from ' String 2 '), character 1 can only be a single character.

1. Trim () deletes the spaces on either side of the string.
2. LTrim () deletes the space to the left of the string.
3. RTrim () deletes the space to the right of the string.
4. Trim (' character 1 ' from ' String 2 ') starts from both sides of the character 2 string, and deletes the specified character 1.
5. Trim ([leading | trailing | both] Trim_char from string) deletes the specified character Trim_char from string strings.
Leading: Deletes from the header of the string.
Trailing: Deletes from the tail of the string.
Borth: Delete from both sides of the string.
6. Tim () can only remove half-width spaces.

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

The trim function in

Oracle's trim function

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

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

Here if the leading indicates a string that matches from the deletion of the header, if the trailing represents a string that matches from the deletion of the tail, if both is indicated, or if no location is indicated, both ends are deleted

If you do not specify any matching string or numeric value, it is considered a space, that is, the preceding or trailing spaces are deleted. The

Trim function returns a type that is VARCHAR2

The following are some examples:

truncate the back of the number 7500 two 0

sql> Select trim (0 from 7500) from Dual

TRIM

--

"

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

sql> Select TRIM (' From ' mid-autumn August ') a s poems from dual;

Poem

——————

Autumn August


The following example truncates the ' half of the second half of the night ' in front of a ' semi ' word

sql> Select trim (leading ' half ') From ' Midnight two more Half ') as poem from dual;

Poem

————————

Night Two more half