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

MySQLMySQLi Database

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)
raja
Published on 20-Feb-2018 09:10:27
Advertisements