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
Learning MySQL and MariaDB: Heading in the Right Direction with MySQL and MariaDB by Russell J. T. Dyer.azw3
Learning MySQL and MariaDB: Heading in the Right Direction with MySQL and MariaDB by Russell J. T. Dyer.mobi
Learning MySQL and MariaDB: Heading in the Right Direction with MySQL and MariaDB by Russell J. T. Dyer.pdf
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.
Algorithms of the Intelligent Web by Haralambos Marmanis;Dmitry Babenko(8293)
Azure Data and AI Architect Handbook by Olivier Mertens & Breght Van Baelen(6678)
Building Statistical Models in Python by Huy Hoang Nguyen & Paul N Adams & Stuart J Miller(6654)
Serverless Machine Learning with Amazon Redshift ML by Debu Panda & Phil Bates & Bhanu Pittampally & Sumeet Joshi(6526)
Data Wrangling on AWS by Navnit Shukla | Sankar M | Sam Palani(6317)
Driving Data Quality with Data Contracts by Andrew Jones(6269)
Machine Learning Model Serving Patterns and Best Practices by Md Johirul Islam(6030)
Learning SQL by Alan Beaulieu(5988)
Weapons of Math Destruction by Cathy O'Neil(5778)
Big Data Analysis with Python by Ivan Marin(5333)
Data Engineering with dbt by Roberto Zagni(4334)
Solidity Programming Essentials by Ritesh Modi(3980)
Time Series Analysis with Python Cookbook by Tarek A. Atwan(3836)
Pandas Cookbook by Theodore Petrou(3546)
Blockchain Basics by Daniel Drescher(3292)
Hands-On Machine Learning for Algorithmic Trading by Stefan Jansen(2900)
Feature Store for Machine Learning by Jayanth Kumar M J(2808)
Learn T-SQL Querying by Pam Lahoud & Pedro Lopes(2791)
Mastering Python for Finance by Unknown(2743)
