TRIM Functions

When processing data using SQL, situations may arise where you need to remove some spaces or specified characters from the original string. The TRIM() function can be used in such situations. In this topic, we will talk about this function and how to use it in MySQL.

The TRIM() function

The TRIM() function in MySQL is used to remove leading and trailing spaces or other specified characters from a string. The function has the following syntax:

TRIM([BOTH | LEADING | TRAILING [remove_string] FROM] string)

The BOTH, LEADING and TRAILING parameters are optional and are used to indicate which side to remove characters from. The remove_string and FROM keyword is also optional and used to specify the string to be removed from the original string. Thus, the most primitive way to use this function is as follows:

TRIM(string)

Such a call of a function will just remove the spaces from both sides of the specified string.

Let's take a look at an instance. We have a string with some unnecessary spaces in it: " Anne ". To remove them, we will use the TRIM() function:

TRIM("  Anne  ")

As a result, we will get a string without any spaces in it: "Anne".

Another simple way to use TRIM() is to remove specific characters or substrings from the string using FROM keyword. Let's now remove the a letter from our " Anne " string.

The result will be the following: " nne ". As we can see, only the specified character was removed, the spaces are still there. From this, we can conclude that the TRIM(string) is equivalent to the TRIM(" " FROM string).

Using TRIM() with parameters

Now let's talk about these parameters you have seen in the previous paragraph. The BOTH parameter is used to remove the specified substring or spaces, if no substring is specified, from both sides of the original string.

For example, for our " Anne " string the result of the TRIM(BOTH FROM " Anne ") will be also equivalent to just TRIM(" Anne ") and we will get the same "Anne" string as a result.

The different things are with LEADING and TRAILING parameters. As you might have guessed, these parameters are used to remove substrings on the left and on the right side of the string.

The result for the TRIM(LEADING FROM " Anne ") command will be the "Anne " and the result for the TRIM(TRAILING FROM " Anne ") will be the " Anne".

Also, we can use these parameters not only with spaces but with substrings too. For instance, we have the following string:

str = "John Bob Alice John"

As we can see, we have a "John" on both sides of the string, and we can remove this substring using these parameters. To remove the substring from the left of the string, we will use LEADING:

TRIM(LEADING "John" FROM str)

And we will get: " Bob Alice John".

And to delete this substring on the right, we use TRAILING parameter:

TRIM(TRAILING "John" FROM str)

And we get the expected result: "John Bob Alice ".

Practical usage of TRIM()

The TRIM() function can be used in many ways, but the most logical is to use it in SELECT and UPDATE statements. For example, we have the following companies table:

Suppose we want to display the website address of each company but without the www. at the beginning. To do this, we can use the TRIM() function inside the SELECT statement:

SELECT
    TRIM(LEADING 'www.' FROM website)
FROM
    companies

After the execution, we will get the list of websites without www. in them:

Now let's assume that we have some spaces before and after the company names in this table:

We don't need to have any extra spaces in the table. To fix this, we can use the TRIM() with the UPDATE statement:

UPDATE
   companies
SET
   company = TRIM(BOTH FROM company)
             // TRIM(company) is also possible

After executing this command, the table will look like this, without any extra spaces in company names:

Conclusion

In this topic, we have analyzed TRIM() command in MySQL. Here are some keynotes from this topic:

• The TRIM() function is used to remove some substrings or spaces at the end or beginning of a string.
• The TRIM() function can't remove anything from the middle of a string.
• There are BOTH, LEADING and TRAILING parameters that set in which part of the string to delete.
• You can set a substring to be deleted with the FROM keyword.
• The syntax of this function may differ in different DBMSs. Refer to the documentation if you intend to use this function for more than MySQL.

I guess, now you are ready to apply your new knowledge! Good luck!