Database Reference
In-Depth Information
on theright,
RTRIM()
will remove them. A more versatile trimming function, though, is
TRIM()
. With it, you can trim both left and right spaces.
These trim functions can be useful for cleaning data with the
UPDATE
statement. Let's
look at an example of their use. In these SQL statements, we'll use
LTRIM()
and
RTRIM()
to eliminate both leading and trailing spaces:
UPDATE
humans
SET
name_first
=
LTRIM
(
name_first
),
name_last
=
LTRIM
(
name_last
);
UPDATE
humans
SET
name_first
=
RTRIM
(
name_first
),
name_last
=
RTRIM
(
name_last
);
In this example, we trimmed the leading spaces with thefirst
UPDATE
and the trailing
spaces with the second one. Notice that we set the value of the columns to the same val-
ues, but with the strings trimmed. We can combine these functions into one SQL state-
ment like so:
UPDATE
humans
SET
name_first
=
LTRIM
(
RTRIM
(
name_last
) ),
name_last
=
LTRIM
(
RTRIM
(
name_last
) );
You can always combine functions like this for a more dynamic result. In this case,
though, the
TRIM()
function isa better alternative. Here's the same SQL statement using
it:
UPDATE
humans
SET
name_first
=
TRIM
(
name_first
),
name_last
=
TRIM
(
name_last
);
The
TRIM()
function also offers more options. You can specify something other than
spaces to remove. For instance, suppose we receive a small table with bird sightings from
another bird-watcher club, as we did in
Row Subqueries
. However, in this table, the sci-
entific names of bird species are within double quotes. If we wanted to insert that data into
our
bird_sightings
table, we could use the same SQL query as we did before, with
the addition of the
TRIM()
function. Here is the relevant excerpt, the last lines on which
we join their table to our
birds
table:
…
JOIN
rookery
.
birds
ON
(
scientific_name
=
TRIM
(
BOTH
'"'
FROM
science_name
) ) );
