Learning MySQL and MariaDB: Heading in the Right Direction with MySQL and MariaDB by Russell J. T. Dyer

Author:Russell J. T. Dyer [Dyer, Russell J. T.]
Language: eng
Format: epub, azw3, mobi, pdf
Tags: COMPUTERS / Programming Languages / SQL
ISBN: 9781449362843
Publisher: O'Reilly Media
Published: 2015-03-29T21:00:00+00:00

Replacing and Inserting into Strings

If you want to insert or replace certain text from a column (but not all of its contents), you could use the INSERT() function. Don’t confuse this with 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 statement:

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 argument 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 example 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 abbreviated in the birds table (e.g., Great is abbreviated as Gt.). We prefer not to have any abbreviations 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 example. The extra twist here is in the second argument, which contains the LOCATE(). We’re using that function to determine the position in the string where text is to be replaced. 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.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.