Introduction to oracle trim function usage, oracletrim

Introduction to oracle trim function usage, oracletrim

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 removes the specified content before and after the string (by default, spaces are deleted ).

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.

The following is a supplement from other netizens:

For example, trim ('character 1' from 'string 2'), Character 1 can only be a single character.

1. trim () deletes spaces on both sides of the string.
2. ltrim () deletes spaces on the left of the string.
3. rtrim () deletes spaces on the right of the string.
4. trim ('character 1' from 'string 2') deletes the specified character 1 from both sides of string 2.
5. trim ([leading | trailing | both] trim_char from string) deletes the specified character trim_char from the String.
Leading: deletes a string from its header.
Trailing: deletes a string from its tail.
Borth: Delete the string from both sides.
6. tim () can only delete spaces.

For example:
Trim ('tech ') wocould return 'tech ';
Trim (''from 'tech ') wocould return 'tech ';
Trim (leading '0' from '20140901') wocould return '20140901 ';
Trim (trailing '1' from 'tech1') wocould return 'tech ';
Trim (both '1' from '123tech111') wocould return '23tech ';

Trim functions in oracle

The trim function in Oracle is used to delete the given characters in the header or tail of a given string or given number.
Trim functions have the following forms:

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

Here, if leading indicates that the string matches the header is deleted, if trailing indicates that the string matches the tail is deleted, if both is specified, or no position is specified, both ends will be deleted.

If no matching string or value is specified, it is considered as a space, that is, the leading or trailing space is deleted.

The type returned by the trim function is varchar2.

The following are some examples:

The two zeros following the number 7500 are truncated.

Select trim (0 from 7500) from dual;

TRIM

--

75

In the following example, the word 'between the two 'Before and After 'mid-autumn August' is truncated.

SQL> select trim ('中' from 'mid-autumn Mid-Autumn mid-October 10') as poem from dual;

Poetry

------

Autumn August


In the following example, we cut the word 'half 'in front of 'half a half in the middle of the night.

SQL> select trim (leading 'half' from 'midnight two') as poem from dual;

Poetry

--------

Midnight update

Which of the following functions can remove spaces before and after data in oracle?

Select trim ('a AA') from dual;
Select Ltrim ('a AA') from dual;
Select Rtrim ('a AA') from dual;
Select Rtrim (Ltrim ('a AA') from dual;
These three functions.

How to Use the TRIM function in Excel

Trim is explained in help. Removes spaces before and after the cell content.
For example
= Trim (a1)
If there is a space before and after the value in a1, this formula can only display the content except the space before and after.