MySQL TRIM() Function

Summary: in this tutorial, you will learn hơ 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 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 the 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 str 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. 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.

1) 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)

Try It Out

2) 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)

Try It Out

It is equivalent to the following statement that uses LTRIM() function:

SELECT LTRIM('    MySQL TRIM Function   ');Code language: SQL (Structured Query Language) (sql)

3) 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)

Try It Out

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)

4) 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).

5) Using TRIM() function to update data in a table

In case the data is 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:

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 can 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)

Try It Out

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)

Try It Out

In this tutorial, you have learned how to use the TRIM function to remove the unwanted leading and trailing characters from a string.