Analysis of MySQL's method of implementing fuzzy string replacement based on regular expressions

This article uses an example to describe how to use MySQL to implement fuzzy string replacement based on regular expressions. Share with you for your reference, the details are as follows:

For example: abcd(efg)hijk after replacement is abcdhijk

Copy the code as follows:

 update tabaleA set name = replace(name, substring(name, locate('<contact>', name), locate('</contact>', name)-locate('<contact>'+10, name)),'');

After execution, an error is reported: Truncated incorrect DOUBLE value

The solution is that after querying, it was found that the problem was caused by concat(Str,'') function. Some DBs support the + operator, while others do not and must use the concat function.

Modify the SQL as follows:

Copy the code as follows:

 update t_global_project set name = replace(name, substring(name, locate('<contact>', name), locate('</contact>', name)-locate(concat('<contact>','10'), name)),'');

PS: Here are two very convenient regular expression tools for your reference:

JavaScript regular expression online testing tool:
http://tools.jb51.net/regex/javascript

Regular expression online generation tool:
http://tools.jb51.net/regex/create_reg

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

Complete guide to using REGEXP regular expressions in MySQL
Analysis of the usage of replace and regexp for regular expression replacement in MySQL
mysql regular expression LIKE wildcard
How to use regular expression query in mysql
mysql regular expression query contains non-digits and characters records
Detailed explanation of MySQL syntax, special symbols and regular expressions
Detailed explanation of MySql basic query, join query, subquery, regular expression query
Summary of classic examples of MySQL query operations using regular expressions
MySQL Regular Expressions Tutorial
Example analysis of the search function of MySQL regular expressions (regexp and rlike)

<<: Two ways to completely delete users under Linux

>>: Summary of Vue's common APIs and advanced APIs

Detailed explanation of the application of meta tags in mobile platform development

Analysis of MySQL's method of implementing fuzzy string replacement based on regular expressions

Detailed explanation of the application of meta tags in mobile platform development

Detailed explanation of Linux host name modification command

How to set up swap partition SWAP in Linux 7.7

MYSQL slow query and log example explanation

In-depth discussion on auto-increment primary keys in MySQL

Docker installation and configuration command code examples

Linux directory switching implementation code example

Example of using CSS3 to customize the style of input multiple-select box

Solution to the lack of my.ini file in MySQL 5.7

MySQL database deletes duplicate data and only retains one method instance

Recommend

Detailed explanation of the solution to Ubuntu dual system stuck when starting

Detailed tutorial on installing phpMyAdmin on Ubuntu 18.04

Usage instructions for the docker create command

MySQL table field time setting default value

How to prevent computer slowdown when WIN10 has multiple databases installed

Implementation of Mysql User Rights Management

MySQL 8.0.26 installation and simplified tutorial (the most complete on the Internet)

Basic use of subqueries in MySQL

Sample code for nginx to achieve dynamic and static separation

About MySQL innodb_autoinc_lock_mode

How to view Docker container application logs

Nginx monitoring issues under Linux

Docker Detailed Illustrations

Ubuntu builds Mysql+Keepalived high availability implementation (dual-active hot standby)

Web interview frequently asked questions: the principles and differences between reflow and repaint