Oracle trim function Usage Details, oracletrim

Oracle trim function Usage Details, oracletrim

In oracle databases, 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.
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.jbxue.com> 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.jbxue.com> 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.jbxue.com> select trim (trailing from 'dweye ') "TRIM e.g." from dual;
TRIM e.g.
------------
DWEYE
Gloria@www.jbxue.com> 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.jbxue.com> 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.jbxue.com> select trim (both 'X' from 'xxxxdweyexxxx') "TRIM e.g." from dual;
TRIM e. g
--------
DWEYE
Gloria@www.jbxue.com> select trim (trailing 'X' from 'xxxxdweyexxxx') "TRIM e.g." from dual;
TRIM e.g.
------------
XxxxDWEYE
Gloria@www.jbxue.com> 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.jbxue.com> 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.jbxue.com> select rtrim ('xyxxdweyexyyx', 'xy') "e.g." from dual;
E.g.
------------
XyxxDWEYE
2) use LTRIM
Gloria@www.jbxue.com> 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.jbxue.com> 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

How to use trim Functions

Trim (): deletes spaces at the beginning and end of a string. Syntax: Trim (string)
Syntax of various trim functions:
LTRIM (string): removes spaces on the left of the string.
RTRIM (string): removes spaces on the right of the string.
TRIM (string): removes spaces at the beginning and end of the string.
ALLTRIM (string): removes spaces on both sides of the left and right sides of the string.

How to use trim functions? Let's take an example.

In addition to a single space between words, clear all spaces in the text. You can use the TRIM function to retrieve text with irregular spaces from other applications.
Syntax
TRIM (text)
Text to clear the Text with spaces.
Example
TRIM ("First Quarter Earnings") equals to "First Quarter Earnings"