MySQL TRIM Function

MySQL TRIM function is one of the String Functions, which is useful to remove or delete prefixes, suffixes, or empty spaces from both sides of a string or any specified side of a expression. Or say, MySQL TRIM function removes leading and trailing spaces or characters or substrings from a given string.

Let me show you how to write MySQL String TRIM Function query to remove prefixes and suffixes from a given expression with an example. The basic syntax of TRIM of a string in MySQL is as shown below:


TRIM(LEADING 'Character or Substring' FROM 'String_Expression')

TRIM(BOTH 'Character or Substring' FROM 'String_Expression')

TRIM(TRAILING 'Character or Substring' FROM 'String_Expression')

MySQL TRIM Example

In this example, we used this MySQL TRIM function to remove leading and trailing spaces or remove empty spaces from both sides of the given string and return the remaining sentence. The following query shows multiple ways to use this one.

SELECT TRIM('     Hi');

SELECT TRIM('Hello ');

SELECT TRIM(' Hello World ');
MySQL TRIM Function 1

In this example, we are using this MySQL Trim to remove characters or special characters @ and $ from both sides (if any) of a given string

SELECT TRIM('@' FROM '@@@@@@TutorialGateway@@@@');

SELECT TRIM('@' FROM '@@@@@@TutorialGateway');

SELECT TRIM('$' FROM 'TutorialGateway$$$$$$$$');
MySQL TRIM Function 2

You can use this MySQL TRIM function to remove prefixes or suffixes based on the LEADING or TRAILING term. Although they act as LTRIM and RTRIM, it is more powerful. Because this String method will not only remove empty spaces, it can remove characters and substring.

SELECT TRIM(LEADING '$' FROM '$$$$$$TutorialGateway$$$$$$');

SELECT TRIM(TRAILING '$' FROM '$$$$$$TutorialGateway$$$$$$');

SELECT TRIM(BOTH '$' FROM '$$$$$$TutorialGateway$$$$$$');
MySQL TRIM Function 3

This is another example of this method.

SELECT TRIM(BOTH 'a' FROM 'aaaaaMySQLaaaaaa');

SELECT TRIM(LEADING 'xyz' FROM 'xyzaaaaaMySQLaaaaaaxyz');

SELECT TRIM(TRAILING 'xyz' FROM 'xyzaaaaaMySQLaaaaaaxyz');
MySQL TRIM Function 4

MySQL String TRIM Example 3

The MySQL String TRIM function also allows you to remove suffixes and prefixes from a column data. In this example, We are going to use this method on the Email column

SELECT FirstName,
       TRIM('.com' FROM Email),
       TRIM('ab' FROM Email)
 FROM employe;
MySQL TRIM Function 5