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