How to Use the MySQL TRIM() Function
In this tutorial, we'll learn how to use the MySQL TRIM() function. TRIM() is a function to remove all prefixes and suffixes, including leading and trailing spaces, from a given string.
MySQL TRIM() Syntax
The syntax of the TRIM() function is as follows:
TRIM([BOTH | LEADING | TRAILING] remove_string FROM string)
or
TRIM([remove_string FROM] string)
Remarks
- The argument string is required. It's a text string to remove, all prefixes and suffixes, including leading and trailing spaces.
- If none of the specifiers, LEADING, TRAILING or BOTH is given, BOTH is used.
- Remove_string is optional, and if it's not specified, spaces are removed.
Examples
Example 1: Remove Leading and Trailing Spaces
The following statement removes all leading and trailing spaces from the specified text string:
SELECT TRIM(" This tutorial teaches you how to use one of the useful functions. ");
The returned text string from this statement:
This tutorial teaches you how to use one of the useful functions.
Example 2: Remove Leading and Trailing Spaces
The following statement removes all leading and trailing spaces from the specified text string:
SELECT TRIM(BOTH " " FROM " This tutorial teaches you how to use one of the useful functions. ");
Result:
This tutorial teaches you how to use one of the useful functions.
Example 3: Remove a Substring from the Prefix of the String
The following statement removes The from the prefix of the string:
SELECT TRIM(LEADING "The" FROM "The SQL Tutorial");
Result:
SQL Tutorial
Example 4: Remove a Substring from the Suffix of the String
The following statement removes Tutorial from the suffix of the string:
SELECT TRIM(TRAILING "Tutorial" FROM "The SQL Tutorial");
Result:
The SQL
Example 5: Remove a Substring from Both Prefix and Suffix of the String
The following statement removes Tutorial from both prefix and suffix of the string:
SELECT TRIM(BOTH "Tutorial" FROM "Tutorial SQL Tutorial");
or:
SELECT TRIM("Tutorial" FROM "Tutorial SQL Tutorial");
Result:
SQL
Example 6: Remove Occurrences of a Character
The following statement removes all Xs from the prefix and suffix of the string:
SELECT TRIM("X" FROM "XXX HOME XXXXX");
Result:
HOME
In this tutorial, we've learned how to use the MySQL TRIM() function. TRIM() is useful when you want to remove all prefixes and suffixes, including leading and trailing spaces, from a given string.