Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 multibyte-safe.

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 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 and 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 occurrences of consecutive commas with a single comma:

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