How can we remove all the prefixes or suffixes from a given string in MySQL?

MySQL TRIM() function is used to remove all the suffixes or prefixes or both from the string. The working of TRIM() function can be understood with the help of its syntax −

Syntax

TRIM([{BOTH | LEADING | TRAILING} [str_to_remove] FROM] string)

Here, 

• the argument BOTH means the prefixes from both left and right to be removed from the string.
• LEADING argument means that only leading prefixes to be removed.
• TRAILING argument means that only trailing prefixes to be removed.
• Str_to_remove is the argument which means the string we want to remove from the string.
• String argument means the string from which the prefixes have to be removed.

Example

mysql> Select TRIM(BOTH '0' FROM '0100');
+----------------------------+
| TRIM(BOTH '0' FROM '0100') |
+----------------------------+
| 1                          |
+----------------------------+
1 row in set (0.00 sec)

mysql> Select TRIM(BOTH 'AB' FROM 'ABCDAB');
+-------------------------------+
| TRIM(BOTH 'AB' FROM 'ABCDAB') |
+-------------------------------+
| CD                            |
+-------------------------------+
1 row in set (0.00 sec)

mysql> Select TRIM(Trailing 'AB' FROM 'ABCDAB');
+-----------------------------------+
| TRIM(Trailing 'AB' FROM 'ABCDAB') |
+-----------------------------------+
| ABCD                              |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> Select TRIM(Leading 'AB' FROM 'ABCDAB');
+----------------------------------+
| TRIM(Leading 'AB' FROM 'ABCDAB') |
+----------------------------------+
| CDAB                             |
+----------------------------------+
1 row in set (0.00 sec)

Akshaya Akki

Updated on: 20-Jun-2020

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started