MySQL has a number of available String Functions, allowing you to work with string data values in a multitude of ways. In this blog post, I explore and learn about the REPLACE function, used to replace specific sections of a string…
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 5.7.24
The REPLACE function syntax (from the documentation) is as follows:
1 | REPLACE(str,from_str,to_str) |
The parameters mean:
- str : The ‘searched’ string, returned once function execution completes.
- from_str : This string is what will be replaced in the ‘searched’ string.
- to_str : This string is what does the replacing in the ‘searched’ string.
Below is a simple example, using a value we perhaps need to convert to a valid date data type for further storage and processing:
1 2 3 4 5 6 7 | mysql> SELECT REPLACE('12-09-99', '-', ','); +-------------------------------+ | REPLACE('12-09-99', '-', ',') | +-------------------------------+ | 12,09,99 | +-------------------------------+ 1 row in set (0.02 sec) |
Visible in the returned query result, the string ’12-09-99′ (which is intended to be a date value in a ‘Month-Day-Year’ format), each occurrence of a dash (-), has been replaced by a comma (,).
I’ll apply this to a practical use-case. First, assign the returned value to a user-defined session variable (see the blog post, MySQL Transactions and User-defined session variables I wrote about these handy variables):
1 2 | mysql> SET @some_date = REPLACE('12-09-99', '-', ','); Query OK, 0 rows affected (0.00 sec) |
Then, use that session variable in a call to the STR_TO_DATE function, performing the conversion to a valid MySQL date data type:
1 2 3 4 5 6 7 | mysql> SELECT STR_TO_DATE(@some_date, '%m, %d, %Y'); +---------------------------------------+ | STR_TO_DATE(@some_date, '%m, %d, %Y') | +---------------------------------------+ | 1999-12-09 | +---------------------------------------+ 1 row in set (0.01 sec) |
Want to know more about the STR_TO_DATE function? Read my blog post, Structuring candidate date values with the STR_TO_DATE function in MySQL – with examples.
REPLACE does perform a ‘case-sensitive’ search on the ‘searched’ string parameter.
See with this next arbitrary example:
1 2 3 4 5 6 7 | mysql> SELECT REPLACE('Josh','j','G'); +-------------------------+ | REPLACE('Josh','j','G') | +-------------------------+ | Josh | +-------------------------+ 1 row in set (0.00 sec) |
Notice, no part of the string was replaced in that function call.
Why is that?
To understand, note the difference in case for the letter ‘J’ with the same query:
1 2 3 4 5 6 7 | mysql> SELECT REPLACE('Josh','J','G'); +-------------------------+ | REPLACE('Josh','J','G') | +-------------------------+ | Gosh | +-------------------------+ 1 row in set (0.00 sec) |
By this example, you can see that case does matter for the from_str parameter.
In the next example, we will know how REPLACE can help us with a simple typo in our data.
Suppose I have a site_promotion table, holding promotional text data for a website:
1 2 3 4 5 | mysql> CREATE TABLE site_promotion( -> promo_id INTEGER PRIMARY KEY, -> promo_body TEXT, -> promo_date DATETIME DEFAULT CURRENT_TIMESTAMP()); Query OK, 0 rows affected (0.39 sec) |
Below is the stored data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> SELECT * FROM site_promotion\G *************************** 1. row *************************** promo_id: 17 promo_body: This is our amazing promotional add for this week only! Get half off the cover price for the next 7 days only! Do not miss this deal!!! promo_date: 2018-11-15 07:14:26 *************************** 2. row *************************** promo_id: 91 promo_body: What?! Prefer to do all your shopping online? Hey who doesn't! No worries. Visit us at wwc.salesgalore.com for all the latest deals you cannot refuse! Again, that is wwc.salesgalore.com promo_date: 2018-11-15 07:18:43 *************************** 3. row *************************** promo_id: 111 promo_body: Happy 4th Sale! We have offers to good to be true!! promo_date: 2018-11-15 07:15:21 3 rows in set (0.00 sec) |
You will notice in the promo_body column for the row containing promo_id of 91, there are typos for the ficticious website address, wwc.salesgalore.com. It should instead read ‘www’.
Easily fixed with the REPLACE function:
1 2 3 4 5 | mysql> UPDATE site_promotion -> SET promo_body = REPLACE(promo_body,'wwc','www') -> WHERE promo_id = 91; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
Note: You should absolutely confirm the WHERE clause boolean conditional is correct or you could implement changes you did not intend -i.e., other rows, wrong row, entire table, etc…
Let’s revisit the updated data for that row:
1 2 3 4 5 6 7 8 | mysql> SELECT * -> FROM site_promotion -> WHERE promo_id = 91\G *************************** 1. row *************************** promo_id: 91 promo_body: What?! Prefer to do all your shopping online? Hey who doesn't! No worries. Visit us at www.salesgalore.com for all the latest deals you cannot refuse! Again, that is www.salesgalore.com promo_date: 2018-11-15 07:18:43 1 row in set (0.00 sec) |
Both instances of ‘wwc’ have been replaced by ‘www’.
For more information, visit the REPLACE function to determine how it can be used for your specific use cases through the link to the official documentation below.
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
Explore the official MySQL 5.7 Online Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
One thought on “Useful String Function – REPLACE in MySQL with examples.”