TRIM( )

This MySQL function returns the given string with any trailing or leading padding removed, depending on which is specified.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

This function returns the given string with any trailing or leading padding removed, depending on which is specified. If neither is specified, BOTH is the default, causing both leading and trailing padding to be removed. The default padding is a space if none is specified. The function is multi-byte safe.

Examples

As an example, in a table containing the results of a student survey we notice that one of the columns that lists each student's favorite activities contains extra commas at the end of the comma-separated list of activities. This may have been caused by a problem in the web interface, which treated any activities that a student didn't select as blank values separated by commas at the end (e.g., biking,reading,,,,).

UPDATE student_surveys
SET favorite_activities =
TRIM(LEADING SPACE(1) FROM TRIM(TRAILING ',' FROM favorite_activities));

In this example, we're using TRIM() twice: once to remove the trailing commas from the column favorite_activities and then again on those results to remove spaces leading spaces. Since the functions are part of an UPDATE statement, the double trimmed results are saved back to the table for the row for which the data was read. This is more verbose than it needs to be, though. Because a space is the default padding, we don't have to specify it. Also, because we want to remove both leading or trailing spaces and commas from the data, we don't have to specify LEADING or TRAILING and can allow the default of BOTH to be used. Making these adjustments, we get this tighter SQL statement:

UPDATE student_surveys
SET favorite_activities =
TRIM(TRIM(',' FROM favorite_activities));

If we suspected that the faulty web form also added extra commas between the text (not just at the end), we could wrap these concentric uses of TRIM() within REPLACE() to replace any occurences of consecutive commas with a single comma:

UPDATE student_surveys
SET favorite_activities =
REPLACE(TRIM(TRIM(',' FROM favorite_activities)), ',,', ',');