Name
TRIM()
Synopsis
TRIM([[BOTH|LEADING|TRAILING] [padding
] FROM]string
)
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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.