MySQL TRIM Function
Summary: in this tutorial, we will show you how to use the MySQL TRIM()
function to remove the unwanted leading and trailing characters from a string.
Introduction to the MySQL TRIM()
function
The data from the user’s input is typically not what we expected. Sometimes, it is not well-formed, for example, wrong cases, or some even contain leading and trailing spaces and also other unwanted characters.
To keep the data in the correct format, before inserting or updating data in the database, you need to clean it up. One of the most important tasks in data cleansing is to remove unwanted leading and trailing characters.
MySQL provides a very useful string function named TRIM()
to help you clean up the data. The following illustrates the syntax of the TRIM()
function.
TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str);
Code language: SQL (Structured Query Language) (sql)
The TRIM
function provides a number of options. You can use the LEADING
, TRAILING
, or BOTH
option to explicitly instruct the TRIM()
function to remove leading, trailing, or both leading and trailing unwanted characters from a string.
By default, the TRIM()
function uses the BOTH
option.
The [removed_str]
is the string that you want to remove. If you don’t specify a specific string, the TRIM()
function removes spaces only.
The st
r is the string that you want to remove the removed_str
.
The TRIM()
function returns a string that has unwanted characters removed.
Note that to remove the leading spaces from a string, you use the LTRIM()
function. And to remove trailing spaces from a string, you use the RTRIM()
function.
MySQL TRIM()
function examples
Let’s take some examples of using the MySQL TRIM()
function.
A) Using MySQL TRIM()
function to remove both leading and trailing spaces
The following statement uses the TRIM()
function to remove both leading and trailing spaces from a string.
SELECT TRIM(' MySQL TRIM Function ');
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-TRIM-BOTH-example.jpg?resize=204%2C70)
B) Using MySQL TRIM()
function to remove only leading spaces
The following statement uses the TRIM()
function to remove only leading spaces.
SELECT TRIM(LEADING FROM ' MySQL TRIM Function ');
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-TRIM-LEADING-space.jpg?resize=322%2C87)
It is equivalent to the following statement that uses LTRIM()
function:
SELECT LTRIM(' MySQL TRIM Function ');
Code language: SQL (Structured Query Language) (sql)
C) Using MySQL TRIM()
function to remove only trailing spaces
The following statement uses the TRIM()
function to remove only trailing spaces from a string.
SELECT TRIM(TRAILING FROM ' MySQL TRIM Function ');
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-TRIM-TRAILING-example.jpg?resize=302%2C88)
It is equivalent to the following statement that uses the RTRIM()
function:
SELECT TRIM(TRAILING FROM ' MySQL TRIM Function ');
Code language: SQL (Structured Query Language) (sql)
D) Using MySQL TRIM()
function to remove the newline characters
The following statements remove the newline characters at the end of a string.
SELECT
TRIM(TRAILING '\n' FROM field_name)
FROM table_name;
Code language: SQL (Structured Query Language) (sql)
SELECT
TRIM(TRAILING '\r' FROM field_name)
FROM table_name;
Code language: SQL (Structured Query Language) (sql)
SELECT
TRIM(TRAILING '\r\n' FROM field_name)
FROM table_name;
Code language: SQL (Structured Query Language) (sql)
Note that based on the platform, the new line could be \n (Unix or Linux), \r (Mac), or both (\r\n).
E) Using MySQL TRIM()
function to update data in a table
In case the data already in the database and you want to clean up the spaces or any other unwanted characters, you can use the TRIM()
function in the UPDATE
statement.
We will take the products
table from the sample database for the demonstration.
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/products_table-6.png?resize=181%2C231)
The following statement uses the TRIM() function to remove all spaces from productname
column in the products
table.
UPDATE products
SET
productname = TRIM(productname);
Code language: SQL (Structured Query Language) (sql)
Notice that the TRIM()
function only removes the unwanted leading and/ or trailing characters from a string. If you want to remove the unwanted characters in the middle of a string, you should use the REPLACE function instead.
MySQL LTRIM()
and RTRIM()
functions
If you want to remove only leading or trailing spaces, you can use other string functions: LTRIM()
and RTRIM()
.
The following statement uses the LTRIM()
function to remove the leading spaces of a string.
SELECT LTRIM(' MySQL LTRIM function');
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-LTRIM-example.jpg?resize=206%2C69)
The following statement uses the RTRIM()
function to remove the trailing spaces of a string.
SELECT RTRIM('MySQL RTRIM function ');
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-RTRIM-example.jpg?resize=207%2C68)
In this tutorial, you have learned how to use the TRIM
function to remove the unwanted leading and trailing characters from a string.