Oracle TRIM function syntax

Oracle TRIM functions are common functions. the syntax of Oracle TRIM functions is described in detail below. We hope you can have a deeper understanding of Oracle TRIM functions.

If Oracle TRIM function is mentioned, the simplest function is to use it to remove spaces at the beginning and end of the line of a string. This function is also the most frequently used one.
However, Oracle TRIM functions are actually capable of deleting "any specified" characters. Let's have a tour.

1. Let's take a look at the complete syntax description of Oracle TRIM functions.
TRIM ([{LEADING | TRAILING | BOTH}
[Trim_character]
| Trim_character
}
FROM
]
Trim_source
)

The above syntax is derived from Oracle 10gR2 official documentation: http://download.oracle.com/docs/... 0/img_text/trim.htm
From the syntax definition, we can see that the small Oracle TRIM function contains more customizable functions. One by one for your reference.

2. The simplest usage begins.
No parameters are used:
Gloria@www.dweye.net> select trim ('dweye ') "TRIM e.g." from dual;

TRIM e. g
--------
DWEYE

This is also the most common method of using default parameters. By default, TRIM deletes spaces before and after strings.

3. In fact, the first common method is equivalent to the method with the "BOTH" parameter below.
Gloria@www.dweye.net> select trim (both from 'dweye ') "TRIM e.g." from dual;

TRIM e. g
--------
DWEYE

The "BOTH" parameter indicates that spaces are deleted by default when the specified content before and after the string is removed ).

4. Now that you have tried the BOTH parameter, let's take a look at the effects of the "TRAILING" and "LEADING" parameters.
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

It can be seen that the "TRAILING" parameter can be used to delete spaces at the end of a string. The "LEADING" parameter is the opposite, which completes the deletion of spaces at the string header.
That is to say, the "TRAILING" and "LEADING" parameters can be used to specify the location for space deletion.

5. "trim_character" parameter debut
This parameter changes the default behavior of deleting spaces.
If you want to delete the "x" before and after the string 'xxxxdweyexxxx', the "trim_character" parameter will be used.
Gloria@www.dweye.net> select trim ('x' from 'xxxxdweyexxx') "TRIM e.g." from dual;

TRIM e. g
--------
DWEYE

The following figure shows how to use the parameters BOTH, TRAILING, and LEADING. Check the results. Do not repeat them.
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. Notes
The "trim_character" parameter can contain only one character, but cannot contain multiple characters.
The error message is as follows:
Gloria@www.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 shocould have only one character

Since TRIM cannot meet the requirements for deleting only the "DWEYE" string, is there any other means? Of course. We use RTRIM and LTRIM to complete this task.
1) Use RTRIM
Gloria@www.dweye.net> select rtrim ('xyxxdweyexyyx', 'xy') "e.g." from dual;

E.g.
------------
XyxxDWEYE

2) use LTRIM
Gloria@www.dweye.net> select ltrim ('xyxxdweyexyyx', 'xy') "e.g." from dual;

E.g.
------------
DWEYExyyx

3) use the RTRIM and LTRIM functions together to achieve our goal.
Gloria@www.dweye.net> select ltrim (rtrim ('xyxxdweyexyyx', 'xy'), 'xy') "e.g." from dual;

E.g.
--------
DWEYE

Note when using the RTRIM and LTRIM functions: "xy" does not indicate that the entire "xy" string is matched, but any character "x" or "y" is found to be deleted.

7. Summary
While enjoying the convenience of Oracle functions, we recommend that you trace the source of each common function. You may find that: Oh, it turns out that these methods are often used only for their real functions.

Oracle date functions

Introduction to Oracle System Variable Functions

Syntax for creating an Oracle package

Use of Oracle to_char Functions

Learn about the Oracle FBI Index