Analysis of the usage of replace and regexp for regular expression replacement in MySQL

This article uses examples to illustrate the use of replace and regexp to replace regular expressions in MySQL. Share with you for your reference, the details are as follows:

Today a friend asked me how to modify the formats similar to “./uploads/110100_cityHotel_北京富豪豪酒店.jpg” found in the database to “./uploads/110100cityHotel北京富豪豪酒店.jpg”. I have never processed data in this way, but I know that MySQL can do it using replace, and regular expressions can also do it.

How to do it?

We only need one sentence like this:

update master_data.md_employee set name=replace(name,"_",'') where id = 825;

-- Note replace(field name,"characters to be replaced","characters to be replaced"), that's it.

In MySQL, replace and regexp are mainly used to replace data through SQL statements.

Let's first talk about the specific usage of replace.

mysql replace usage

1. replace into

is equivalent to

insert into table (id,name) values('1′,'aa'),('2′,'bb')

If the same value exists, the data will not be inserted.

2. replace(object,search,replace)

Replace all occurrences of search in object with replace

select replace('www.jb51.net','w','Ww')

—>WwWwWw.jb51.net

Example: Replace aa in the name field in the table table with bb

update table set name=replace(name,'aa','bb')

Another type of pattern matching provided by MySQL is using extended regular expressions.

When you test for a match on such patterns, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some characters for extended regular expressions are:

· '.' matches any single character.

· The character class "[...]" matches any character within the square brackets. For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a "-". "[az]" matches any letter, and "[0-9]" matches any digit.

· “ * ” matches zero or more of the character that precedes it. For example, "x*" matches any number of "x" characters, "[0-9]*" matches any number of digits, and ".*" matches any number of any characters.

If the REGEXP pattern matches anywhere in the value being tested, the pattern matches (this differs from LIKE pattern matching, which matches only if it matches the entire value).

To position a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.

To illustrate how extended regular expressions work, here's the LIKE query shown above rewritten using REGEXP:

1. To find names that begin with "d", use "^" to match the beginning of the name:

SELECT * FROM master_data.md_employee WHERE name REGEXP '^d';

Such result sets are case-insensitive. If you want to force the REGEXP comparison to be case-sensitive, use the BINARY keyword to make one of the strings a binary string. This query matches only names that start with a lowercase 'd'.

SELECT * FROM master_data.md_employee WHERE name REGEXP BINARY'^d';

To find names ending with "love", use "$" to match the end of the name:

SELECT id,name FROM master_data.md_employee WHERE name REGEXP 'love$';

To find names that contain a "w", use the following query:

SELECT id,name FROM master_data.md_employee WHERE name REGEXP 'w';

Since a regular expression pattern matches if it appears anywhere in a value, you don't have to put a wildcard on either side of the pattern in the previous query to make it match the entire value, as you would if you used an SQL pattern.

To find names that contain exactly 5 characters, use "^" and "$" to match the beginning and end of the name, and 5 instances of "." in between:

SELECT id,name FROM master_data.md_employee WHERE name REGEXP '^.....$';

You can also rewrite the previous query using the "{n}" "repeat n times" operator:

SELECT id,name FROM master_data.md_employee WHERE name REGEXP '^.{5}$';

This is some simple usage of MySQL replace and regexp. For in-depth learning, we will write specific examples and usage in subsequent articles.

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.