In MySQL, replaceregexp is used for regular expression replacement.

In MySQL, replaceregexp is used for regular expression replacement.

This article describes how to replace regular expressions with replace and regexp in MySQL. We will share this with you for your reference. The details are as follows:

Today, a friend asked me to change the format similar to "./uploads/110100_cityhotel_beijingfu .jpg" in the database to "./uploads/110100cityhotelbeijingfu .jpg. I have never processed data like this, but I know mysql can use replace, and the regular expression can also.

What should we do?

We only need such a statement,
Copy codeThe Code is as follows: update master_data.md_employee set name = replace (name, "_", '') where id = 825;

-- Note replace (field name, "character to be replaced", "character to be replaced.

In Mysql, replace and regexp use SQL statements to replace data.

Let's talk about the specific usage of replace.

Mysql replace usage

1.replace into
Copy codeThe Code is as follows: replace into table (id, name) values ('1', 'A'), ('2', 'bb ')
This statement inserts two records into the table. If the primary key id is 1 or 2 does not exist

It is equivalent
Copy codeThe Code is as follows: insert into table (id, name) values ('1', 'A'), ('2', 'bb ')
Data is not inserted if the same value exists.

2.replace(object,search,replace)

Replace all search objects with replace
Copy codeThe Code is as follows: select replace ('www .jb51.net', 'w', 'ww ')
-> WwWwWw.jb51.net

For example, replace aa in the name field of the table with bb.
Copy codeThe Code is as follows: update table set name = replace (name, 'AA', 'bb ')
Other types of pattern matching provided by MySQL use extended regular expressions.

When you perform a match test on this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ).

Some Characters of the extended regular expression are:

· '.' Matches any single character.

· The character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name the character range, use a hyphen (-). "[A-z]" matches any letter, and "[0-9]" matches any number.

· "*" Matches zero or multiple characters before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number, and ". * "matches any number of characters.

If the REGEXP pattern matches any part of the tested value, the pattern matches (different from the LIKE pattern match. The pattern matches only the entire value ).

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

To demonstrate how the extended regular expression works, use REGEXP to rewrite the LIKE Query shown above:

1. To find the name starting with "d", use "^" to match the name:
Copy codeThe Code is as follows: SELECT * FROM master_data.md_employee WHERE name REGEXP '^ d ';
These result sets are case-insensitive. If you want to force REGEXP to be case-sensitive, use the BINARY keyword to convert a string to a BINARY string. This query only matches the lowercase 'D' of the first letter of the name '.
Copy codeThe Code is as follows: SELECT * FROM master_data.md_employee WHERE name regexp binary '^ d ';
To find the name ending with "love", use "$" to match the end Of the name:
Copy codeThe Code is as follows: SELECT id, name FROM master_data.md_employee WHERE name REGEXP 'Love $ ';
To locate the name containing "w", use the following query:
Copy codeThe Code is as follows: SELECT id, name FROM master_data.md_employee WHERE name REGEXP 'W ';
Since a regular expression appears anywhere in the value, its pattern matches, you do not have to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value, just as if you were using an SQL mode.

To locate a name that contains exactly five characters, use "^" and "$" to match the start and end of the name, and the five "." instances are in the range:
Copy codeThe Code is as follows: SELECT id, name FROM master_data.md_employee WHERE name REGEXP '^... $ ';
You can also use the "{n}" "Repeat n times" operator to overwrite the previous query:
Copy codeThe Code is as follows: SELECT id, name FROM master_data.md_employee WHERE name REGEXP '^. {5} $ ';
This knowledge provides some simple mysql replace and regexp usage. For in-depth learning, we will write down the specific examples and usage in subsequent articles.

PS: here we will provide two very convenient Regular Expression tools for your reference:

Javascript Regular Expression online testing tool:
Http://tools.jb51.net/regex/javascript

Regular Expression generation tool:
Http://tools.jb51.net/regex/create_reg