Replace, regexp Regular expression substitution usage in MySQL

MySQL field values are replaced by the following practices:

The code is as follows	Copy Code
Update ' Table_hospital ' Set service=replace (service, "<a%</font></a>", "") <BR type= "_moz" >

Can limit the ID, scope, specific content to play their own imagination!

The use of Replace and regexp in MySQL database is mainly to realize the substitution of data through SQL statement.

MySQL Replace usage
1.replace into

The code is as follows	Copy Code
Replace into table (Id,name) VALUES (' 1′, ' AA '), (' 2′, ' BB ')

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

The code is as follows	Copy Code
Insert into table (Id,name) VALUES (' 1′, ' AA '), (' 2′, ' BB ')

Data is not inserted if the same value exists

2.replace (Object,search,replace)
Replaces all occurrences of search in object with replace

The code is as follows	Copy Code
Select replace (' Www.111cn.net ', ' w ', ' Ww ')->wwwwww.111cn.net To replace a AA in the Name field in table tables with BB 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 match this pattern to a test, 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.
· Character class "[...]" Matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name the range of characters, use a "-". "[A-z]" matches any letter, and "[0-9]" matches any number.
· "*" matches 0 or more characters in front of 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 anywhere that is tested, the pattern matches (unlike like pattern matching, which 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 beginning of the pattern or at the end of the pattern with "$".
To illustrate how an extended regular expression works, use RegExp to rewrite the like query shown above:
To find the name that begins with "B", use "^" to match the beginning of the name:

The code is as follows	Copy Code
Mysql> SELECT * from pet WHERE name REGEXP ' ^b '; +--–+--–+ ——— +--+ ———— + ———— + | name| Owner | Species | sex | Birth | Death | +--–+--–+ ——— +--+ ———— + ———— + | Buffy | Harold | Dog| f | 1989-05-13 | null| | Bowser | Diane | Dog| m | 1989-08-31 | 1995-07-29 | +--–+--–+ ——— +--+ ———— + ———— +

If you want to force the regexp comparison to be case-sensitive, use the binary keyword to make one of the strings into a binary string. The query matches only the lowercase ' b ' of the first letter of the name.

The code is as follows

Copy Code

mysql> SELECT * from pet WHERE name REGEXP BINARY ' ^b '; to find the name ending with "FY", use "$" to match the end of the name: mysql> SELECT * from pet WHERE name REGEXP ' fy$ '; +--–+--–+ ——— +--+ ———— + ——-+ | name| owner | species | sex | birth | death | +--–+--–+ ——— +--+ ———— + ——-+ | Fluffy | Harold | Cat| f | 1993-02-04 | NULL | | Buffy | Harold | Dog| f | 1989-05-13 | NULL | +--–+--–+ ——— +--+ ———— + ——-+ to find the name containing a "W", use the following query: mysql> SELECT * from pet WHERE name REGEXP ' W '; + ———-+ ——-+ ——— +--+ ———— + ———— + | name| owner | species | sex | birth | death | + ———-+ ——-+ ——— +--+ ———— + ———— + | Claws | Gwen | Cat| m | 1994-03-17 | null| | bowser| Diane | Dog| m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | Bird | NULL | 1997-12-09 | null| + ———-+ ——-+ ——— +--+ ———— + ———— +

Since a regular expression appears anywhere in the value and its pattern matches, there is no need to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value, just as you would use an SQL schema.
To find names containing exactly 5 characters, use "^" and "$" to match the start and end of the first name, and the 5 "." Instances in between:

The code is as follows	Copy Code
Mysql> SELECT * from pet WHERE name REGEXP ' ^.....$ '; + ——-+--–+ ——— +--+ ———— + ——-+ | name | Owner | Species | sex | Birth | Death | + ——-+--–+ ——— +--+ ———— + ——-+ | Claws | gwen| Cat| m | 1994-03-17 | NULL | | Buffy | Harold | Dog| f | 1989-05-13 | NULL | + ——-+--–+ ——— +--+ ———— + ——-+

You can also rewrite the previous query using the "{n}" "Repeat N Times" operator:

The code is as follows	Copy Code
Mysql> SELECT * from pet WHERE name REGEXP ' ^. {5}$ '; + ——-+--–+ ——— +--+ ———— + ——-+ | name | Owner | Species | sex | Birth | Death | + ——-+--–+ ——— +--+ ———— + ——-+ | Claws | gwen| Cat| m | 1994-03-17 | NULL | | Buffy | Harold | Dog| f | 1989-05-13 | NULL | + ——-+--–+ ——— +--+ ———— + ——-+

Appendix G:mysql Regular Expressions provide detailed information about the syntax of regular expressions.
^
The matched string starts with the following string

The code is as follows

Copy Code

mysql> Select "Fonfo" REGEXP "^fo$"; -> 0 (indicates no match) mysql> Select "Fofo" REGEXP "^FO"; -> 1 (matching) $ The matched string ends with the preceding string Mysql> Select "Fono" REGEXP "^fono$"; -> 1 (matching) Mysql> Select "Fono" REGEXP "^fo$"; -> 0 (indicates no match) . Match any character (including new rows) mysql> Select "Fofo" REGEXP "^f.*"; -> 1 (matching) mysql> Select "Fonfo" REGEXP "^f.*"; -> 1 (matching) A * Match any number of a (including empty strings) mysql> Select "Ban" REGEXP "^ba*n"; -> 1 (matching) mysql> Select "Baaan" REGEXP "^ba*n"; -> 1 (matching) mysql> Select "Bn" REGEXP "^ba*n"; -> 1 (matching) A + Matches any sequence of 1 or more a characters. mysql> Select "Ban" REGEXP "^ba+n"; -> 1 (matching) mysql> Select "Bn" REGEXP "^ba+n"; -> 0 (indicates no match) A? Match one or 0 a mysql> Select "Bn" REGEXP "^ba?n"; -> 1 (matching) mysql> Select "Ban" REGEXP "^ba?n"; -> 1 (matching) mysql> Select "Baan" REGEXP "^ba?n"; -> 0 (indicates no match) De|abc Match de or ABC mysql> Select "PI" REGEXP "Pi|apa"; -> 1 (matching) Mysql> select "Axe" REGEXP "Pi|apa"; -> 0 (indicates no match) Mysql> Select "APA" REGEXP "Pi|apa"; -> 1 (matching) Mysql> Select "APA" REGEXP "^ (Pi|apa) $"; -> 1 (matching) mysql> Select "PI" REGEXP "^ (Pi|apa) $"; -> 1 (matching) mysql> select "Pix" REGEXP "^ (Pi|apa) $"; -> 0 (indicates no match) (ABC) * Match any number of ABC (including Empty strings) mysql> Select "PI" REGEXP "^ (pi) *$"; -> 1 (matching) mysql> Select "Pip" REGEXP "^ (pi) *$"; -> 0 (indicates no match) mysql> Select "Pipi" REGEXP "^ (pi) *$"; -> 1 (matching) {1} {2,3}

This is a more comprehensive approach that can implement the functionality of several reserved words in the previous
*
can be written as a{0,}
A
can be written as A{1,}
a?
can be written as a{0,1}
There is only one integer parameter I in {}, indicates that the character can only appear I; there is an integer parameter I in {},
followed by a ",", which indicates that the character may appear in I or above; only one integer parameter I in {}, followed by a
. ", followed by an integer parameter J, indicates that the character can only appear above I, J times the following
(including I and J times). The integer argument must be greater than or equal to 0, and less than or equal to Re_dup_max (the default is
5). If both M and n,m must be less than or equal to N.
[A-DX], [^A-DX]
matches any character that is (or is not, if used ^) A, B, C, D, or X. The "-" character between two other characters constitutes a range that matches all characters from the beginning of the 1th character to the 2nd character. For example, [0-9] matches any decimal digit. To include the literal character "]", it must be immediately after the opening parenthesis "[". To include the literal character "-", it must be written first or last. for [] Any character that does not have any special meaning defined internally, only matches itself.

The code is as follows

Copy Code

mysql> Select "AxBC" REGEXP "[a-dxyz]"; -> 1 (matching) mysql> Select "AxBC" REGEXP "^[a-dxyz]$"; -> 0 (indicates no match) mysql> Select "AxBC" REGEXP "^[a-dxyz] $"; -> 1 (matching) mysql> Select "AxBC" REGEXP "^[^a-dxyz] $"; -> 0 (indicates no match) mysql> Select "Gheis" REGEXP "^[^a-dxyz] $"; -> 1 (matching) mysql> Select "Gheisa" REGEXP "^[^a-dxyz] $"; -> 0 (indicates no match) [[. characters.]]

Represents the order in which elements are compared. The order of characters within parentheses is unique. However, the parentheses can contain wildcard characters,
So he can match more characters. For example: Regular expressions [[. Ch.]] *c matches the first five characters of CHCHCC
。
[=character_class=]
A class that represents equality and can replace other equal elements in a class, including itself. For example, if O and () are
A member of an equal class, then [[=o=]], [[=]] and [O ()] are completely equivalent.
[: Character_class:]
Inside the brackets, in [: And:] The middle is the name of the character class, which can represent all characters belonging to this class.
The names of the character classes are: alnum, digit, punct, alpha, graph, space, blank, lower, Uppe
R, Cntrl, print and Xdigit
mysql> Select "Justalnums" REGEXP "[[: Alnum:]]"; -> 1 (matching)
mysql> Select "!!" REGEXP "[[: Alnum:]]"; -> 0 (indicates no match)
Alnum literal numeric character
Alpha literal characters
Blank whitespace characters
Cntrl control characters
Digit numeric characters
Graph graphic Characters
Lower lowercase text characters
Print graphic or space character
Punct punctuation Characters
Space spaces, tabs, new lines, and carriage returns
Upper uppercase literal characters
Xdigit hexadecimal digit character
[[:;:]]
[[::]]
An empty string that matches the beginning and end of a word, and the beginning and end of the word are not included in the Alnum
Character can not be underlined.
Mysql> Select "A word a" REGEXP "[[: <:]]word[[:>:]]"; -> 1 (matching)
Mysql> Select "A Xword a" REGEXP "[[: <:]]word[[:>:]]"; -> 0 (indicates no match)
Mysql> Select "Weeknights" REGEXP "^ (Wee|week) (knights|nights) $"; -> 1 (indicates
Match
To use a literal instance of a special character in a regular expression, precede it with a 2 backslash "" character. The MySQL resolver is responsible for interpreting one of these, and the regular expression Library is responsible for interpreting the other. For example, to match the string "1+2" that contains the special character "+", in the following regular expression, only the last one is correct:

The code is as follows	Copy Code
mysql> SELECT ' 1+2′regexp ' 1+2′; -> 0 mysql> SELECT ' 1+2′regexp ' 1+2′; -> 0 mysql> SELECT ' 1+2′regexp ' 1\+2′; -> 1

First of all, describe the problems encountered:
The following is a table mt2 in the database:

The code is as follows

Copy Code

+----+-------- ----------------------------------+ | id | name                                       | +----+------------------------------------------+ |  1 | sdfsf<contact>beijing</contact> sldjfsld  | |  2 | sdfsf<contact>shanghai</contact>sldjfsld | |  3 | sdfsf<contact>jn</contact>sldjfsld       | |  4 | sdfsf<contact>qd</contact>sldjfsld       | +----+------------------------------------------+

The requirement encountered is to delete the contents of <contact> to </contact> in this table.
As we all know, the Replace function does not support regular expressions, so it can only be handled in other ways.
So, I used the following SQL statement:

The code is as follows	Copy Code
Update MT2 Set name = replace (name, substring (name, locate (' <contact> ', name), locate (' </contact> ', name)- Locate (' <contact> ' +10, name), ");

String (Regular) Blur

The problem has been solved.

Results:

The code is as follows	Copy Code
+----+-------------------+ | ID | name | +----+-------------------+ | 1 | SDFSFACTSLDJFSLD | | 2 | SDFSFACTSLDJFSLD | | 3 | SDFSFACTSLDJFSLD | | 4 | SDFSFACTSLDJFSLD | +----+-------------------+

The functions that are used are described below:
Locate

LOCATE (SUBSTR,STR)
POSITION (substr in str)
Returns the position of the substring substr the first occurrence in string str. If substring substr does not exist in STR, the return value is 0:

Substring

SUBSTR (Str,pos,len): Starting from <pos> position in <str>, the next <len> characters are selected.

Replace

Replace (str1, str2, STR3): In the string str1,? STR2 out?? R,?⑵ Huang Str3 substitution.

Disadvantage: Only one occurrence of <contact> to </contact> can occur, many times can only be divided into multiple replacements