How can I eradicate some specific suffix or prefix or both from a MySQL string?

MySQL TRIM() function is used to eradicate a specific suffix or prefix 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)

Sharon Christine

e

Updated on: 20-Jun-2020

183 Views

Kickstart Your Career

Get certified by completing the course

Get Started