Common usage of regular expressions in Mysql

Common usage of Regexp in Mysql

Fuzzy matching, containing specific strings
# Find records containing "Car Club" in the content field

select * from club_content where content regexp 'Car Club'

# The following usage of regexp and like is equivalent

select * from club_content where content like '%车友俱乐部%'

Fuzzy matching, starting with a specific string
# Find the records starting with "车友" in the content field

select * from club_content where content regexp '^车友'

# The following usage of regexp and like is equivalent

select * from club_content where content like '车友%'

Fuzzy matching, ending with a specific string
# Find records ending with "车友" in the content field

select * from club_content where content regexp '车友$'

# The following usage of regexp and like is equivalent

select * from club_content where content like '%车友'

Fuzzy matching, or relationship
# Search for "experience", "share" or "technical post" in the content field

select * from club_content where content REGEXP 'Experience|Share|Technical Post'

Fuzzy matching, not including single characters
# Find records that do not contain the word "车" or "友" in the content field

select * from club_content where content REGEXP [^rider]

I was shocked when I saw the result. All the records were displayed. Why?
Because once the square brackets "[]" are added, the content inside will be split into individual characters and then matched. It will match each character one by one to determine whether it is equal to "车" or "友". The returned result is a set of logical values ​​0 and 1.

If you want to match a string that does not contain a specific string, how can you achieve it?

Fuzzy matching, not containing a specific string
# Find records whose content field does not contain the string "车友"

select * from club_content where content not REGEXP 'rider'

MySql REGEXP operator to match string

1 ^ matches the string that starts with the character following this character. For example: REGEXP '^x' means matching the character that starts with x.
2 $ matches the string ending with the character before it. For example: REGEXP 'y$' means matching the character ending with y
3. Match any character
4 [...] Matches any one character in the square brackets.
For example: [1-9] matches numbers from 1 to 9, [abc] matches any one of them
5 * matches zero or more characters preceding it, such as: x* matches any number of x characters

How to determine whether a string is a number in mysql

This question is a bit strange, but many times we store numbers in the form of strings, and when we use strings for mathematical operations, it seems that nothing goes wrong. Unless, the string used for mathematical operations cannot be converted into a number.
But how do we determine whether a string can be converted into a number?

Use MySQL's REGEXP operator. How to use it?

{String} REGEXP '[^0-9.]'

The string in front is what we need to judge, and the string behind is the MySQL regular expression, which means matching characters that are not numbers or decimal points.

If the string contains a number other than 0-9 or a decimal point, it returns true, otherwise it returns false.

For example:

select ('123a' REGEXP '[^0-9.]'); --'123a' contains the character 'a'. The output is 1. The constant true in MySQL is output as 1 and false is output as 0.

Note: If there are spaces in the string, the regular expression will also match and return 1. If you want to remove the spaces at both ends, you need to use the trim() function on the string.

This is just a simple application of the REGEXP operator. For detailed application of REGEXP, please refer to the official documentation.

MySQL Regular Expressions

In the previous chapters, we have learned that MySQL can perform fuzzy matching through LIKE ...%.

MySQL also supports matching of other regular expressions. The REGEXP operator is used in MySQL for regular expression matching.

If you know PHP or Perl, this is pretty straightforward since MySQL's regular expression matching is similar to those scripts.

The regular expression patterns in the following table can be used with the REGEXP operator.

Examples

After understanding the above regular requirements, we can write SQL statements with regular expressions according to our needs. Below we will list a few small examples (table name: person_tbl) to deepen our understanding:

Find all data starting with 'st' in the name field:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

Find all data ending with 'ok' in the name field:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

Find all data containing the string 'mar' in the name field:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

Find all data in the name field that starts with a vowel character or ends with the string 'ok':

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

MySQL regular REGEXP learning practice notes

REGEXP is a function used to execute regular expressions in MySQL, like preg in PHP. If the regexp regular function is just a simple query, you can use like, but if it is complex, you still need to use regexp. Let's take a look.

The MySQL user manual recommends that you still use wildcards when constructing simple queries.

like:

Select [*|fieldname list] From [tablename] where [fieldname] like ["%someletter"|"%someletter%","_","?someletter"];

But in some special queries, regular expressions are not acceptable. MYSQL provides three regular expression WHERE predicates, namely:

REGEXP, RLIKE, NOT RLIKE

Use these three to replace the original LIKE predicate, which can be followed by a regular expression.
For example, to query data containing "_" in a field, use the following query statement:

SELECT * FROM TABLENAME WHERE FIELDNAME RLIKE '.[_].';

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 find names that begin with "b", use "^" to match the beginning of the name:
Use regular expressions

SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^…..$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

I encountered such a problem in the application today.

There is a field t1 with values ​​like: 1,1,1,2,3,3,4,4,5,5,2,4,3,2,1,2
You need to search for, for example: the number before the first comma is between 3-5, the number before the third comma is between 3-5, the number before the tenth comma is between 3-5, and the rest are between 1-5. . .
The sql statement can be written as follows:

SELECT * FROM tb WHERE t1 REGEXP '^[3-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5]%';

1. Use LIKE and NOT LIKE comparison operators (note that you cannot use = or !=);
2. The mode is case-insensitive by default;
3. Allow "_" to match any single character, and "%" to match any number of characters (including zero);

Attach some mysql regular rules

^ matches the beginning of a string
$ matches the end of a string
. matches any character (including carriage return and newline)
a* matches any sequence of 0 or more a characters
a+ matches any sequence of 1 or more a characters
a? matches 0 or 1 a character
de|abc matches the sequence de or abc
(abc)* matches 0 or more instances of the sequence adc

{n}, {m,n} The {n} or {m,n} notation provides a more general way of writing regular expressions, capable of matching many preceding atoms (or "parts") of the pattern. Both m and n are integers.
a* can be written as a{0,}
a+ can be written as a{1,}
a? can be written as a{0,1}

[a-dX] matches any character that is a, b, c, d, or X. The '-' character between two other characters forms a range.
[^a-dX] matches any character that is not a, b, c, d or X. The preceding character '^' means negation.

[.characters.] In bracket expressions (using [ and ]), matches a sequence of characters used for collating elements, where characters are single characters or character names such as newline.
mysql> SELECT '~' REGEXP '[[.~.]]'; -> 1
mysql> SELECT '~' REGEXP '[[.tilde.]]'; -> 1

[=character_class=]
Within a bracket expression (using [ and ]), [=character_class=] represents an equality class. It matches all characters with the same collation value, including itself.
[[=a=]] is equivalent to [a(+)], [a+], [a{1,}]

[:character_class:]
Within a bracket expression (using [ and ]), [:character_class:] represents a character class that matches all characters of the term class.

The standard class names are:

alnum Alphanumeric character
Alpha text characters
blank
cntrl control character
digit numeric character
graph graphic character
lower lowercase literal characters
print graphics or space characters
punct punctuation character
space Space, tab, new line, and carriage return
upper Uppercase text characters
xdigit Hexadecimal digit character

[[:<:]], [[:>:]]

These markers indicate word boundaries. They match the beginning and end of a word respectively. A word is a sequence of word characters that is not preceded or followed by word characters. character is an alphanumeric character in class alnum or an underscore (_).

mysql> select 'fang shan zi' regexp '[[:<:]]shan[[:>:]]'; -> 1
mysql> select 'fang shan zi' regexp '[[:<:]]fang[[:>:]]'; -> 1
mysql> select 'fang shans zi' regexp '[[:<:]]shan[[:>:]]'; -> 0

Regular expressions use special characters that should be preceded by two backslash characters.

mysql> SELECT '1+2′ REGEXP '1+2′; -> 0
mysql> SELECT '1+2′ REGEXP '1+2′; -> 0
mysql> SELECT '1+2′ REGEXP '1\+2′; -> 1

MySQL search conditions using regular regexp

I use Mybatis

t.hobby ： conditional field
hobby : search parameter, the value can be multiple comma-separated values: 'reading, making friends, go'

<!-- The value of t.hobby may be: 'eating, drinking, whoring, gambling, smoking, cheating, deceiving, kidnapping, deceiving, stealing' There are irregular spaces on both sides of each word -->
<!-- Step by step demonstration below-->
<if test="hobby!=null and hobby!=''">
concat(',',REPLACE (t.hobby, ' ', ''),',') regexp concat(',(',replace(#{hobby},',','|'),'),')
</if>
<!-- Extract the value of table field t.hobby -->
<if test="hobby!=null and hobby!=''">
concat(',',REPLACE (' eat, drink, whore, gamble, smoke, cheat, deceive, kidnap, steal', ' ', ''),',') regexp concat(',(',replace(#{hobby},',','|'),'),')
</if>
<!-- After processing the table field values, extra spaces are removed-->
<if test="hobby!=null and hobby!=''">
concat(',','eat,drink,whore,gamble,smoke,cheat,cheat,kidnap,cheat,steal',',') regexp concat(',(',replace(#{hobby},',','|'),'),')
</if>
<!-- After concat, we get a string with commas at the beginning and end -->
<if test="hobby!=null and hobby!=''">
',eating, drinking, whoring, gambling, smoking, cheating, deceiving, kidnapping, deceiving, stealing,' regexp concat(',(',replace(#{hobby},',','|'),'),')
</if>
<!-- Get the value of parameter #{hobby} -->
<if test="hobby!=null and hobby!=''">
',eating, drinking, whoring, gambling, smoking, cheating, deceiving, kidnapping, cheating, stealing,' regexp concat(',(',replace('eating, drinking, whoring, whoring',',','|'),'),')
</if>
<!-- Replace the comma with | -->
<if test="hobby!=null and hobby!=''">
',eat,drink,whore,gamble,smoke,cheat,cheat,kidnap,cheat,steal,' regexp concat(',(','eat|drink|whore|whore','),')
</if>
<!-- After concat, we get a string with brackets and commas at the beginning and end -->
<if test="hobby!=null and hobby!=''">
',eat,drink,whore,gamble,smoke,cheat,cheat,cheat,steal,' regexp ',(eat|drink|whore|whore),' <!-- In memory of Mr. Yu Qian's virtue of not gambling-->
</if>

The result is 1, so the condition is true.

Complex procedures are mainly used to handle search conditions. It's ok to get the regular condition that meets the requirements, but I personally think that the more ideal way is:
The searched fields were formatted when the data was first stored: 'eat, drink, whore, gamble, smoke, cheat, deceive, kidnap, rip, steal'
The search conditions can be processed and passed in: ',(eating|drinking|whoring|whoring),'
Then a search can be simplified to this

<if test="hobby!=null and hobby!=''">
concat(',', t.hobby ,',') regexp #{hobby}
</if>

That’s about it. You can basically understand it. If you still can’t understand it, please refer to the related articles below.