MySQL TRIM Function

MySQL has many built-in functions for applying string data. The TRIM() function is one of them. It is used in MySQL queries to remove a specific character or the list of characters from the string data. It is crucial to clean up the data before inserting it into the table or updating the table’s data. The TRIM() function is mainly used for this purpose. Suppose the user types the password value with extra space by mistake. It is necessary to remove the extra space before inserting the value into the table or updating the existing value of the table, or checking the value of the table for authentication. Different uses of the TRIM() function have been shown in this tutorial.

Syntax:

The TRIM() function of MySQL can be used for three types of trimming. The syntax of the TRIM() function is provided below:

• BOTH options are used to remove the character or string from both sides of the main string data.
• The LEADING option is used to remove the character or string from the side of the main string data only.
• The LEADING option is used to remove the character or string from the left side of the main string data only.
• The TRAILING option is used to remove the character or string from the right side of the main string data only.
• If no option and remove_string are used in the function, then the space will be removed from both sides of the main string.

Use of TRIM() Function:

The uses of the TRIM() function with the different options by using string data and the date of the table are provided in this part of this tutorial.

Example 1: Using TRIM() Function Without Option

The use of the TRIM() function without any option is discussed in this example. If no option and remove_string value are passed in the TRIM() function, then the space will be removed from the main string. Here, the output of the TRIM() function has been concatenated with another string to check the space is removed from the right side of the string:

Output:

The following output will appear after executing the previous query. The space has been removed from both sides of the string:

Example 2: Using TRIM() Function With BOTH Option

The use of the TRIM() function with BOTH options and the removed string of different values has been shown by using multiple SELECT statements here.

The following SELECT query will generate the same output as the SELECT query of the previous example. Here, BOTH option has been used without any removed string:

Output:

The following output will appear after executing the previous query:

The removing character with BOTH options has been used in the following SELECT query. It will remove the character, ‘P’, from the string, ‘PHP’ and concatenate the remaining string value, ‘H’ with the string ‘-TEST’. The output will be ‘H-TEST’:

Output:

The following output will appear after executing the previous query:

The removing string with BOTH options has been used in the following SELECT query. It will remove the string, ‘PHP’ from the string, ”PHP Bash Python Java PHP’ and concatenate the remaining string value, ‘Bash Python Java’ with the string ‘-TEST’. The output will be ‘Bash Python Java -TEST’:

Output:

The following output will appear after executing the previous query:

Example 3: Using TRIM() Function With LEADING Option

The use of the TRIM() function with the LEADING option and the removing string has been shown in the following SELECT query. The removing string, ‘PHP’, will be removed from the left side of the main string, ”PHP Bash Python Java PHP’. The output will be ‘Bash Python Java PHP-TEST’.

Output:

The following output will appear after executing the previous query:

Example 4: Using TRIM() Function With the TRAILING Option

The use of the TRIM() function with the TRAILING option and the removing string has been shown in the following SELECT query. The removing string, ‘PHP’ will be removed from the right side of the main string, ”PHP Bash Python Java PHP’. The output will be ‘PHP Bash Python Java -TEST’.

Output:

The following output will appear after executing the previous query:

Example 4: Using a TRIM() function for the Table Data

You have to create a table with data in a MySQL database to check the TRIM() function used for the table data. Run the following query to create a database named test_db:

Run the following statement to use the test_db database as the current database:

Run the following query to create the customers table with four fields:

Run the following query to insert three records into the customers table:

Run the following query to check the original data of the customers table:

Output:

The following output will appear after executing the previous query:

Run the following query to remove the string ‘+88’ from the beginning of the values of the contact_no field of the customers table:

Output:

The following output will appear after executing the previous query:

Conclusion:

The uses of the TRIM() function without any option and with three different options have been shown in this tutorial by using multiple SELECT queries. I hope the purpose of using the TRIM() function in MySQL query will be clear for the new MySQL users. Check out other Linux Hint articles for more tips and tutorials.
/div>