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 an expression. Or say, the MySQL TRIM function removes leading and trailing spaces or characters or substrings from a given string.

Let me show you how to write a 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 is as shown below:

TRIM('String_Expression')

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 Function Example

In this example, we used this 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         ');

In this example, we are using this 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$$$$$$$$');

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$$$$$$');

This is another example of this MySQL method.

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

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

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

TRIM Example 3

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

SELECT FirstName,
       LastName,
       DepartmentName,
       Email,
       TRIM('.com' FROM Email),
       TRIM('ab' FROM Email)
 FROM employe;