{"id":4707,"date":"2013-12-17T23:25:44","date_gmt":"2013-12-18T07:25:44","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4707"},"modified":"2023-10-12T21:59:51","modified_gmt":"2023-10-13T04:59:51","slug":"mysql-trim-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-string-functions\/mysql-trim-function\/","title":{"rendered":"MySQL TRIM() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn h\u01a1 will show you how to use the MySQL TRIM()<\/code> function to remove the unwanted leading and trailing characters from a string.<\/p>\n\n\n\n

Introduction to the MySQL TRIM() function<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

To keep the data in the correct format, before inserting<\/a> or updating<\/a> data in the database, you need to clean it up. <\/p>\n\n\n\n

One of the most important tasks in data cleansing is to remove the unwanted leading and trailing characters.<\/p>\n\n\n\n

MySQL provides a very useful string function named TRIM()<\/code> to help you clean up the data. The following illustrates the syntax of the TRIM()<\/code> function.<\/p>\n\n\n

TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The TRIM<\/code> function provides a number of options. You can use the LEADING<\/code>, TRAILING<\/code>, or BOTH<\/code> option to explicitly instruct the TRIM()<\/code> function to remove leading, trailing, or both leading and trailing unwanted characters from a string.<\/p>\n\n\n\n
By default, the TRIM()<\/code> function uses the BOTH<\/code> option.<\/p>\n\n\n\n
The [removed_str]<\/code> is the string that you want to remove. If you don’t specify a specific string, the TRIM()<\/code> function removes spaces only.<\/p>\n\n\n\n
The st<\/code>r is the string that you want to remove the removed_str<\/code>.<\/p>\n\n\n\n
The TRIM()<\/code> function returns a string that has unwanted characters removed.<\/p>\n\n\n\n
MySQL TRIM() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the MySQL TRIM()<\/code> function.<\/p>\n\n\n\n
1) Using MySQL TRIM() function to remove both leading and trailing spaces<\/h3>\n\n\n\n
The following statement uses the TRIM()<\/code> function to remove both leading and trailing spaces from a string.<\/p>\n\n\n
SELECT<\/span> TRIM<\/span>(' MySQL TRIM Function '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
2) Using MySQL TRIM() function to remove only leading spaces<\/h3>\n\n\n\n
The following statement uses the TRIM()<\/code> function to remove only leading spaces.<\/p>\n\n\n
SELECT<\/span> TRIM<\/span>(LEADING<\/span> FROM<\/span> '    MySQL TRIM Function   '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
It is equivalent to the following statement that uses LTRIM()<\/a><\/code> function:<\/p>\n\n\n
SELECT<\/span> LTRIM<\/span>('    MySQL TRIM Function   '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using MySQL TRIM() function to remove only trailing spaces<\/h3>\n\n\n\n
The following statement uses the TRIM()<\/code> function to remove only trailing spaces from a string.<\/p>\n\n\n
SELECT<\/span> TRIM<\/span>(TRAILING FROM<\/span> '    MySQL TRIM Function   '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
It is equivalent to the following statement that uses the RTRIM()<\/a><\/code> function:<\/p>\n\n\n
SELECT<\/span> TRIM<\/span>(TRAILING FROM<\/span> '    MySQL TRIM Function   '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Using MySQL TRIM() function to remove the newline characters<\/h3>\n\n\n\n
The following statements remove the newline characters<\/a> at the end of a string.<\/p>\n\n\n
SELECT<\/span> \n    TRIM<\/span>(TRAILING '\\n'<\/span> FROM<\/span> field_name)\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
SELECT<\/span> \n    TRIM<\/span>(TRAILING '\\r'<\/span> FROM<\/span> field_name)\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
SELECT<\/span> \n    TRIM<\/span>(TRAILING '\\r\\n'<\/span> FROM<\/span> field_name)\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that based on the platform, the new line could be \\n (Unix or Linux), \\r (Mac), or both (\\r\\n).<\/p>\n\n\n\n
5) Using TRIM() function to update data in a table<\/h3>\n\n\n\n
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()<\/code> function in the UPDATE<\/a><\/code> statement.<\/p>\n\n\n\n
We will take the products<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement uses the TRIM() function to remove all spaces from productname<\/code> column in the products<\/code> table:<\/p>\n\n\n
UPDATE<\/span> products \nSET<\/span> \n    productname = TRIM<\/span>(productname);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the TRIM()<\/code> function only removes the unwanted leading and\/ or trailing characters from a string. <\/p>\n\n\n\n
MySQL LTRIM() and RTRIM() functions<\/h2>\n\n\n\n
If you want to remove only leading or trailing spaces, you can use other string functions: LTRIM()<\/code> and RTRIM()<\/code>.<\/p>\n\n\n\n
The following statement uses the LTRIM()<\/code> function to remove the leading spaces of a string.<\/p>\n\n\n
SELECT<\/span> LTRIM<\/span>('  MySQL LTRIM function'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The following statement uses the RTRIM()<\/code> function to remove the trailing spaces of a string.<\/p>\n\n\n
SELECT<\/span> RTRIM<\/span>('MySQL RTRIM function   '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the TRIM<\/code> function to remove the unwanted leading and trailing characters from a string.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
If you want to remove the unwanted characters in the middle of a string, you can use the REPLACE<\/a> function instead.<\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Note that to remove the leading spaces from a string, you use the LTRIM()<\/a><\/code> function. To remove trailing spaces from a string, you use the RTRIM()<\/a><\/code> function.<\/p>\n\n\n\n