Usage Analysis of Regular Expression Replacement with replace and regexp in MySQL

This article illustrates the use of replace and regexp in MySQL for regular expression substitution. Share it for your reference, as follows:

Today, a friend asked me if the format similar to ". /uploads/110100_cityHotel_ Beijing Rich Luxury Hotel. jpg" found in the database was changed to "./uploads/110100cityHotel Beijing Rich Luxury Hotel. jpg". I haven't done this with data myself, but I know that mysql can be done with replace, and so can regular expressions.

How to do it?

We only need one statement like this,

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 mainly implement data replacement through sql statement.

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

Usage of mysql replace

1. replace into

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

The purpose of this statement is to insert two records into the table table. If the primary key id is 1 or 2 does not exist

Is equivalent to

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

No data is inserted if the same value exists

2. replace(object,search,replace)

Replace all search occurring in object with replace

select replace('www.ofstack.com','w','Ww')

- > WwWwWw.ofstack.com

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

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

Other types of pattern matching provided by MySQL are the use of extended regular expressions.

When you test this type of pattern for matching, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The 1 characters of an 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 range of characters, use 1 "-". "[a-z]" matches any letter and "[0-9]" matches any number.

· "*" matches zero or more characters preceding it. For example, "x*" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ".*" matches any number of any characters.

If the REGEXP pattern matches any part of the value under test, the pattern matches (this is different from the LIKE pattern match, which matches only the entire value).

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

To illustrate how extended regular expressions work, rewrite the LIKE query shown above with 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';

This result set is case-insensitive. If you want to force REGEXP comparisons to be case-sensitive, use the BINARY keyword to make one of the strings binary. The query matches only the lowercase 'd' of the first letter of the name.

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

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

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

To find a name that contains 1 "w", use the following query:

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

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

To find a name that contains exactly 5 characters, match the beginning and end of the name with "^" and "$", and 5 "." instances in between:

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

You can also rewrite the previous query using the "{n}" and "Repeat n times" operators:

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

These knowledge 1 some simple mysql replace and regexp usage, for in-depth study, we will later in the article will be specific examples and usage write out

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

JavaScript Regular Expression Online Test Tool:
http://tools.ofstack.com/regex/javascript

Regular expression online generation tool:
http://tools.ofstack.com/regex/create_reg

More readers interested in MySQL can check out the topics on this site: "MySQL Common Functions Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.