Dotlayer
  • News
  • Startups
  • Tutorials
  • SEO
  • Marketing
  • Interviews
No Result
View All Result
Dotlayer
  • News
  • Startups
  • Tutorials
  • SEO
  • Marketing
  • Interviews
No Result
View All Result
Dotlayer
No Result
View All Result

How To Search and Replace Text in MySQL or MariaDB Table

April 16, 2020
in Tutorials
0 0
How To Search and Replace Text in MySQL or MariaDB Table
Share on FacebookShare on Twitter

Have you ever run into the issue where you have a fairly large MySQL table and you would like to replace a string on one or more of the columns?

Let’s say you change the name of your website and there is a particular column with rows that have the old domain name and you would like to change it into the new one. If you have a similar situation then this article will help.

There are actually a couple of ways that you can go about doing this. One way to do this is to export the entire database or just the table into a SQL file and do a regular find and replace with a text-editor of your choice. This is certainly the first way that came to mind when I came across this situation recently.

The only problem with the approach is that if you have a very huge database, then this can take a really long time to accomplish. Solving a problem like this shouldn’t take more that 10 minutes right? That’s what I thought.

Thankfully, MySQL comes with a string replace function which can be used for this exact problem. In this article we will be going over an example of using the REPLACE STRING function in MySQL.

MySQL provides you with a useful string function called REPLACE that allows you to replace a string in a column of a table by a new string.

The syntax of the REPLACE function is as follows:

REPLACE(str,old_string,new_string);

The REPLACE function has three parameters. It replaces the old_string by the new_string in the string

A very important thing to note here is that there is a statement also called REPLACE used to insert or update data. You should not confuse the REPLACE statement with the REPLACE string function.

The REPLACE function is very handy to search and replace text in a table such as updating obsolete URL, correcting a spelling mistake, etc.

The syntax of using the REPLACE function in an UPDATE statement is as follows:

UPDATE tbl_name 
SET 
    field_name = REPLACE(field_name,
        string_to_find,
        string_to_replace)
WHERE
    conditions;

Note that when searching for text to replace, MySQL uses the case-sensitive match to perform a search for a string to be replaced.

MySQL REPLACE string function example use

For example, if you want to correct the domain name scenario I was talking about in the first paragraph. In the table in the sample database table below:

----------------
|domain_names  |
----------------
|example1.com  |
|example2.com  |
|example-2.com |
|example3.com  |
----------------

You can use the REPLACE function as follows:

UPDATE domainList
SET 
    domain_names = REPLACE(domain_names,
        'example-2.com',
        'example2.com');

The query finds all occurrences of the domain about example-2.com and replaces it by the correct value example2.com in the domain_names column of the domain list table.

It is very important to note that in the REPLACE function, the first parameter is the column name without quotes (“). If you put the quotes to the field name like “field_name”, the query will update the content of that column to “field_name”, which is causing unexpected data loss.

The REPLACE function does not support regular expression so if you need to replace a text string by a pattern you need to use MySQL user-defined function (UDF) from external library, check it out here MySQL UDF with Regex. I hope this has been helpful and this little tip will come in handy when you have to deal with such a situation.

ShareTweetPin
Previous Post

Easy SEO Hacks to Optimize Mobile Marketing

Next Post

How to Install and Configure Puppeteer on Ubuntu 18.04 LTS

Next Post
How to Install and Configure Puppeteer on Ubuntu 18.04 LTS

How to Install and Configure Puppeteer on Ubuntu 18.04 LTS

You might also like

Mint Linux vs Ubuntu: Which is Right For You?

Mint Linux vs Ubuntu: Which is Right For You?

March 12, 2022
Net Neutrality: What is it and Why Should You Care?

Net Neutrality: What is it and Why Should You Care?

March 12, 2022
Solid State Drives – Why You Should Buy One Today

Solid State Drives – Why You Should Buy One Today

March 12, 2022

Machine Learning Algorithms Every Beginner Should Know

January 25, 2022
What Is the log4j Vulnerability, log4shell, an Example Step-By-Step Exploit and How to Fixed It

What Is the log4j Vulnerability, log4shell, an Example Step-By-Step Exploit and How to Fixed It

December 11, 2021
Simple Video Call integration into Website with Jitsi

Simple Video Call integration into Website with Jitsi

May 26, 2020
  • Terms of Service
  • Privacy Policy
  • Careers

© 2021 Dotlayer.com

No Result
View All Result
  • About Us
  • Advertise
  • Blog
  • Careers
  • Contact
  • Contact Us
  • Get Featured
  • Home Layout 1
  • Home Layout 2
  • Home Layout 3
  • Privacy Policy
  • Security
  • Services
  • Subscribe To Dotlayer
  • Terms of Service
  • Write For Us

© 2021 Dotlayer.com

Welcome Back!

Login to your account below

Forgotten Password?

Retrieve your password

Please enter your username or email address to reset your password.

Log In