Explore TRIM Functions
The simplest function of Oracle TRIM is to use it to remove spaces at the beginning and end of a string row. This function is also the most frequently used function.
However, the Oracle TRIM function is actually capable of deleting "any specified" characters.
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
)
2. No parameters apply.
SQL> select trim ('helle, redcat') "greeting"
2 from dual;
Greeting
------------
Helle, redcat
This is the most common method of using default parameters. By default, TRIM deletes spaces before and after strings.
It is equivalent to the following statement with the "BOTH" parameter
SQL> select trim (both from 'hello, redcat') "greeting"
2 from dual
3;
Greeting
------------
Hello, redcat
The "BOTH" parameter removes the specified content before and after the string (by default, spaces are deleted ).
The "TRAILING" parameter can delete spaces at the end of a string.
The "LEADING" parameter is the opposite. It deletes spaces in the string header.
3. The "trim_character" parameter changes the default behavior of "deleting spaces.
Trim deletes specified characters on both sides.
SQL> select trim ('s 'from 'sssshello, redcatss') "greeting"
2 from dual;
Greeting
------------
Hello, redcat
Same as above
SQL> select trim (both's 'from 'ssshello, redcatss') "greeting"
2 from dual;
Greeting
------------
Hello, redcat
Leading deletes the specified character from the string Header
SQL> select trim (leading's 'from 'ssshello, redcatss') "greeting"
2 from dual;
Greeting
---------------
Hello, redcatsss
Trailing deletes the specified character at the end of the string
SQL> select trim (trailing's 'from 'ssshello, redcatss') "greeting"
2 from dual;
Greeting
---------------
Ssshello, redcat
The above can only delete a single character, error message ORA-30001: trim set shocould have only one character to delete multiple, you need to use the following method.
4. Use RTRIM and LTRIM to remove the specified multi-Character
Ltrim deletes the specified character on the left of the string.
SQL> select ltrim ('sasaashello, redcatsaassa ', 'sa') "greeting"
2 from dual;
Greeting
------------------
Hello, redcatsaassa
Rtrim deletes the specified character on the right of the string
SQL> select rtrim ('sasaashello, redcatsaassa ', 'sa') "greeting"
2 from dual;
Greeting
------------------
Sasaashello, redcat
Ltrim and rtrim work together to achieve our goal
SQL> select ltrim (rtrim ('sasaashello, redcatsaassa ', 'sa'), 'sa') "greeting"
2 from dual;
Greeting
------------
Hello, redcat
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.
5. insights
A small function can find such a function.