Database Reference
In-Depth Information
where we wanted to insert text and we had to tell it how many characters to replace. A
simpler function for replacing text is
REPLACE()
. We coulduse this function to replace
all occurrences of
Gt.
with
Great
in the
common_name
column. Let's testthat with a
SELECT
statement like so:
SELECT common_name AS Original,
REPLACE(common_name, 'Gt.', 'Great') AS Replaced
FROM birds
WHERE common_name REGEXP 'Gt.' LIMIT 1;
+------------------+--------------------+
| Original | Replaced |
+------------------+--------------------+
| Gt. Reed-Warbler | Great Reed-Warbler |
+------------------+--------------------+
This works much better. We can use the
REPLACE()
with the arguments we have here
and enter the following
UPDATE
to change the data in the table:
UPDATE birds
SET common_name = REPLACE(common_name, 'Gt.', 'Great');
Query OK, 8 rows affected (0.23 sec)
Rows matched: 28891 Changed: 8 Warnings: 0
Notice that we didn't include the
WHERE
clause, but the results message says that only
eight rows were changed. This is because there were only eight rows that contained
Gt.
in
the
common_name
column. Updating data in a table with that many rows is intimidating
and dangerous without a
WHERE
clause. That's why it's good to use them and to test the
parameterswith a
SELECT
statementfirst.
