LTRIM, RTRIM, and TRIM in ORACLE

Usage of LTRIM, RTRIM, and TRIM in ORACLE:
1. LTRIM (C1, C2)
Both C1 and C2 can be strings, for example, C1 is 'Miss Liu ', C2 'misl', and so on. This is the first difference from SQL SERVER. If you remember well, the LTRIM of SQL Server only has one parameter to remove spaces on the left of the string. Oracle's LTRIM ensures that the first character of C1 cannot appear in the C2 string.

SQL> select LTRIM ('Miss Liu ', 'Liu') Result from dual;

RESULT
--------
Miss Liu

 

SQL> select LTRIM ('Miss liu','m is ') result from dual;

RES
---
Liu

 

From the above, we can see the role of LTRIM. However, if the second string is not input, the LTRIM function is the same as that in SQL SERVER, that is, remove spaces on the left.

 

SQL> select ltrim ('Miss liu') result from dual;

RESULT
----------
Miss Liu

SQL> select length ('Miss liu') len1, length (ltrim ('Miss liu') lentrim from dual;

LEN1 LENTRIM
--------------------
12 10

From the above, we can see that the LTrim function of Oracle should be more powerful and be able to operate on the leading character.

2. The function of RTRIM is the same as that of LTRIM, but RTRIM is changed from right to left. In this way, the specific characters in the next guide are removed.

3. TRIM functions are described as follows:

In Oracle/PLSQL, the trim function removes all specified characters either from the beginning or the ending of a string.

The syntax for the trim function is:

Trim ([leading | trailing | both [trim_character] string1)

Leading-remove trim_string from the front of string1.

Trailing-remove trim_string from the end of string1.

Both-remove trim_string from the front and end of string1.

If none of these are chosen (ie: leading, trailing, both), the trim function will remove trim_string from both the front and end of string1.

 

Trim_character is the character that will be removed from string1. If this parameter is omitted, the trim function will remove all leading and trailing spaces from string1.

String1 is the string to trim.

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

 

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

1. Let's take a look at the complete syntax description of the TRIM function.
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/cd/B19306_01/server.102/b14200/img_text/trim.htm
From the syntax definition, we can see that the small TRIM function contains more customizable functions. One by one for your reference.

2. The simplest usage begins.
No parameters are used:
Sec @ ora10g> select trim ('secoler') "TRIM e.g." from dual;

TRIM e. g
--------
SECOOLER

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.
Sec @ ora10g> select trim (both from 'secoler') "TRIM e.g." from dual;

TRIM e. g
--------
SECOOLER

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.
Sec @ ora10g> select trim (trailing from 'secoler') "TRIM e.g." from dual;

TRIM e.g.
------------
SECOOLER

Sec @ ora10g> select trim (leading from 'secoler') "TRIM e.g." from dual;

TRIM e.g.
------------
SECOOLER

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 'xxxxsecoolerxx', the "trim_character" parameter will be used.
Sec @ ora10g> select trim ('x' from 'xxxxsecoolerxx') "TRIM e.g." from dual;

TRIM e. g
--------
SECOOLER

The following figure shows how to use the parameters BOTH, TRAILING, and LEADING. Check the results. Do not repeat them.
Sec @ ora10g> select trim (both 'X' from 'xxxxsecoolerxx') "TRIM e.g." from dual;

TRIM e. g
--------
SECOOLER

Sec @ ora10g> select trim (trailing 'X' from 'xxxxsecoolerxx') "TRIM e.g." from dual;

TRIM e.g.
------------
XxxxSECOOLER

Sec @ ora10g> select trim (leading 'X' from 'xxxxsecoolerxx') "TRIM e.g." from dual;

TRIM e.g.
------------
SECOOLERxxxx

6. Notes
The "trim_character" parameter can contain only one character, but cannot contain multiple characters.
The error message is as follows:
Sec @ ora10g> select trim (leading 'xy' from 'xyxxsecoolerxyyx') "TRIM e.g." from dual;
Select trim (leading 'xy' from 'xyxxsecoolerxyyx') "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 "SECOOLER" string, is there any other means? Of course. We use RTRIM and LTRIM to complete this task.
1) Use RTRIM
Sec @ ora10g> select rtrim ('xyxxsecoolerxyyx', 'xy') "e.g." from dual;

E.g.
------------
XyxxSECOOLER

2) use LTRIM
Sec @ ora10g> select ltrim ('xyxxsecoolerxyyx', 'xy') "e.g." from dual;

E.g.
------------
SECOOLERxyyx

3) use the RTRIM and LTRIM functions together to achieve our goal.
Sec @ ora10g> select ltrim (rtrim ('xyxxsecoolerxyyx', 'xy'), 'xy') "e.g." from dual;

E.g.
--------
SECOOLER

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.