Database Reference
In-Depth Information
Replacing and Inserting into Strings
If you want to insert orreplace certain text from a column (but not all of its contents),you
could use the INSERT() function. Don't confuse thiswith the INSERT statement. The
syntax of this function consists of the string or column into which you want to insert text,
followed by the position in which to insert text. You may specify also how much text to
delete from that point, if you want. Finally, you give the text to insert. Let's look at some
examples of this function.
We'll start with a simple example. Suppose that on a page of the Rookery site, we are
thinking of adding some text to the common names of bird species with the word Least in
their name. We want to explain that it means Smallest , so that uninformed birders don't
think it means these birds are the least important. To test this, we enter this SQL state-
ment:
SELECT INSERT(common_name, 6, 0, ' (i.e., Smallest)')
AS 'Smallest Birds'
FROM birds
WHERE common_name LIKE 'Least %' LIMIT 1;
+------------------------------+
| Smallest Birds |
+------------------------------+
| Least (i.e., Smallest) Grebe |
+------------------------------+
The first argument is the column containing the string we're manipulating. The second ar-
gument is the starting point for inserting text. Based on the WHERE clause, we're looking
for common names that start with Least . That's 5 characters. We add 1 to that because the
starting point for INSERT is 1. The third argument specifies how many characters after
the starting point should be replaced. In this case, we're just inserting text, not replacing
any.
The SQL statement uses INSERT() to change the results set, not the data in the table. So
we could use the INSERT() function to display the common names like this to new
members for the first month who have identified themselves as new to bird-watching. We
would have to construct a more complex SQL statement to check who is new, but this ex-
ample shows you how to insert text within a string. Let's look now at an example in which
we will replace data using INSERT() .
Suppose we discover that parts of some of the common bird species names are abbrevi-
ated in the birds table (e.g., Great is abbreviated as Gt. ). We prefer not to have any ab-
Search WWH ::




Custom Search