Database Reference
In-Depth Information
breviations for the common names. Before changing the data, we'll execute a
SELECT
statement to test our use of the
INSERT()
function:
SELECT common_name AS Original,
INSERT(common_name, LOCATE('Gt.', common_name), 3, 'Great') AS
Adjusted
FROM birds
WHERE common_name REGEXP 'Gt.' LIMIT 1;
+------------------+--------------------+
| Original | Adjusted |
+------------------+--------------------+
| Gt. Reed-Warbler | Great Reed-Warbler |
+------------------+--------------------+
We've already reviewed the arguments of the
INSERT()
function in the previous ex-
ample. The extra twist here is in the second argument, which containsthe
LOCATE()
.
We're using that function to determine the position in the string where text is to be re-
placed. In the previous example, we assumed that the common name would start with the
string we wanted to modify. In this case, we're not assuming the position of the string
within the column. Instead, we're letting MySQL find it for us.
Another difference in this example is the third element: we're telling the function to re-
place three characters (i.e., the length of
Gt.
) from the starting point with the text given for
the fourth argument (i.e.,
Great
). Although the text we're adding is more than three char-
acters, it's fine because when we update the table later, we're updating a column with
plenty of space to hold the results.
If
LOCATE()
does not find the string we give it, it returns 0. A value of 0 for the position
in the
INSERT()
function negates it and returns the value of
common_name
un-
changed. So with this usage of
INSERT()
, because of the inclusion of
LOCATE()
for
the starting location, the
WHERE
clause is unnecessary — except to see that it works
where we want it to.
Now that we've verified that our combination of functions works correctly, we can update
the data by entering the following SQL statement:
UPDATE
birds
SET
common_name
=
INSERT
(
common_name
,
LOCATE
(
'Gt.'
,
common_name
),
3
,
'Great'
)
WHERE
common_name
REGEXP
'Gt.'
;
There is an alternative to using
INSERT()
for replacing text in a string. In the previous
example, we had to use the
LOCATE()
function to determine the location of the text
