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 string 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.

MySQL TRIM Syntax

The basic syntax of TRIM of a string in MySQL 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 Example

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

SELECT TRIM('     Hi');

SELECT TRIM('Hello          ');

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

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

SELECT TRIM('@' FROM '@@@@@@[email protected]@@@');

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

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

MySQL TRIM Example 2

You can use this 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 Function 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 the Trim Function.

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 string trim function on the Email column

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