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

Published on 20-Feb-2018 13:11:05

• Related Questions & Answers
• Remove all duplicates from a given string in Python
• Remove all duplicates from a given string in C#
• How can we remove a column from MySQL table?
• Integer literal in C/C++ (Prefixes and Suffixes)
• Count all prefixes in given string with greatest frequency using Python
• Java program to remove all the white spaces from a given string
• Can we remove a primary key from MySQL table?
• How can we print all the capital letters of a given string in Java?
• How can we delete all rows from a MySQL table?
• How can we fetch all the records from a particular MySQL table?
• How can we extract a substring from a string in MySQL?
• How can we use MySQL TRIM() to remove the whitespaces from all the rows and update table?
• How to remove double or more spaces from a string in MySQL?
• How to remove all non-alphanumeric characters from a string in MySQL?
• How can we import the text file, having some line prefixes, into MySQL table?