Shulou(Shulou.com)05/31 Report--

This article is about how to use Regexp regular expressions in Mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Common usage of Regexp in Mysql

Fuzzy matching, containing specific strings

# find the record that contains "car club" in the content field

Select * from club_content where content regexp 'car Friends Club'

# the following uses of regexp and like are equivalent at this time

Select * from club_content where content like'% car Club%'

Fuzzy match, starting with a specific string

# find the records that start with "cyclists" in the content field

Select * from club_content where content regexp'^ cyclists'

# the following uses of regexp and like are equivalent at this time

Select * from club_content where content like 'cyclists%'

Fuzzy match, ending with a specific string

# find the record that ends with "cyclists" in the content field

Select * from club_content where content regexp 'cyclists $'

# the following uses of regexp and like are equivalent at this time

Select * from club_content where content like'% cyclists'

Fuzzy matching, or relation

# find "experience", "share" or "technical post" in the content field

Select * from club_content where content REGEXP 'experience | share | Technology post'

Fuzzy matching, not containing a single character

# find records that do not contain the words "car" and "friend" in the content field

Select * from club_content where content REGEXP [^ riders]

This result ran out a surprise, unexpectedly all the records to run out, this is why?

Because once you add this square bracket "[]", it will split the contents into individual characters and then match them, and it will match each character to determine whether it is equal to "car" or "friend". The result returned is a set of logical values of 0 and 1.

How do you do that if you want to match without a specific string?

Fuzzy matching, does not contain specific strings

# find records where the content field does not contain a string of "cyclists"

Select * from club_content where content not REGEXP 'cyclists'

MySql REGEXP operator matching string

1 ^ matches a string that begins with the character after that character

For example: REGEXP'^ x 'means to match characters that begin with x

2$ matches a string that ends with the character preceding that character

For example: REGEXP'y characters' means to match characters that end in y

3. Match any character

4 [...] Matches any character in square brackets.

For example, [1-9] matches numbers from 1 to 9, and [abc] matches any one of them.

5 * matches zero or more characters in front of it

For example, x* matches any number of x characters

How does mysql determine whether a "string" is a "number"?

It's a strange question, but most of the time we store numbers in the form of strings, and in turn, we don't seem to make mistakes when we use strings for mathematical operations. Unless a string used for mathematical operations cannot be converted into a number.

But how can we tell whether a string can be converted into a number?

The REGEXP operator of mysql is used. How do I use it?

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

The first string is what we want to judge, and the following string is the regular expression of mysql, which means to match characters that are not numbers or decimal points.

Returns true if the String contains a number other than 0-9 or a decimal point, and vice versa, returns false.

For example:

Select ('123a' REGEXP'[^ 0-9.]');-- '123a' contains the character' a'. The output result is 1 mysql. The constant true output is 1 false output is 0.

Note: if there is a space in the string, it will also match to the regular expression and return 1. If you want to remove the spaces at both ends, you will have to use the trim () function on the judged string.

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

MySQL regular expression

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

MySQL also supports other regular expression matching, using the REGEXP operator in MySQL for regular expression matching.

If you know PHP or Perl, the operation is very simple, because the regular expression matching of MySQL is similar to that of these scripts.

The regular patterns in the following table can be applied to the REGEXP operator.

The pattern description ^ matches the starting position of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after'\ n'or'\ r'. $matches the end of the input string. If the Multiline property of the RegExp object is set, $also matches the position before'\ n'or'\ r'. . Matches any single character except "\ n". To match any character, including'\ n', use a pattern like'[.\ n]'. [...] A collection of characters. Matches any of the characters contained. For example,'[abc] 'can match' a'in 'plain'. [^.] A collection of negative characters. Matches any characters that are not included. For example,'[^ abc] 'can match' p'in 'plain'. P1 | p2 | p3 matches p1 or p2 or p3. For example,'z | food' can match'z'or 'food'. (Z | f) ood' matches "zood" or "food". * matches the previous subexpression zero or more times. For example, zo* can match "z" and "zoo". * is equivalent to {0,}. + matches the previous subexpression one or more times. For example, 'zo+' can match "zo" and "zoo", but not "z". + is equivalent to {1,}. {n} n is a non-negative integer. Match the determined n times. For example,'o {2} 'does not match the' o'in 'Bob', but does match the two o in 'food'. {n st' m} m and n are non-negative integers, where n SELECT name FROM person_tbl WHERE name REGEXP'^ integers

Find all the data in the name field that ends with 'ok':

Mysql > SELECT name FROM person_tbl WHERE name REGEXP 'ok$'

Find all the data in the name field that contains the 'mar' string:

Mysql > SELECT name FROM person_tbl WHERE name REGEXP 'mar'

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

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

Mysql regular REGEXP Learning exercise Notes

REGEXP in mysql is a function used to execute regular expressions, such as preg in php, regexp regular function if only a simple query to use like, but complex still need to use regexp, let's take a look.

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

Such as:

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

However, in some special queries, it is impossible to use regular expressions. There are three regular expression WHERE predicates provided by MYSQL, namely:

REGEXP, RLIKE, NOT RLIKE

Replace the original LIKE predicate with these three, followed by a regular expression.

For example, to query data with "_" in a field, use the following query statement:

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

Some of the characters that extend regular expressions are:

'.' Matches any single character.

Character class "[.]" Matches any character in square brackets. For example, "[abc]" matches "a", "b", or "c". To name the range of characters, use a "-". "[amurz]" matches any letter, while "[0-9]" matches any number.

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

If the REGEXP pattern matches anywhere in the value under test, the pattern matches (unlike LIKE pattern matching, where the pattern matches only if it matches the entire value).

To locate 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 start with "b", use "^" to match the beginning of the name:

Use regularities

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} $'

Today, we have encountered such a problem in the application.

There is a field T1, in which the value is similar to: 1, 1, 1, 1, 1, 1, 2, 3, 3, 3, 3, 4, 4, 5, 5, 5, 4, 4, 5, 5, 5, 4, 4, 4, 5, 5, 5, 4, 4, 4, 4, 5, 5, 5, 4, 4, 4, 1, 4, 4, 4, 4, 5, 5, 5, 4, 4, 4, 1, 4, 4, 4, 5, 5, 5, 4, 4, 1, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 1, 4, 4, 4, 4, 1, 4, 4, 4, 4, 4,

You need to search inside for example: the number range before the first comma is between 3 and 5, the number before the third comma is between 3 and 5, the number before the 10th comma is between 3 and 5, and the rest is between 1 and 5.

Then 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], [1-5], [1-5], [1-5]%'

1. Use the LIKE and NOT LIKE comparison operators (note that you cannot use = or! =)

two。 The mode ignores case by default

3. Allow "_" to match any single character, and "%" to match any number of characters (including zero characters)

Attach some mysql regular rules

^ matches the beginning of the string

$matches the end of the string

. Match any character (including carriage return and new lines)

A * any sequence that matches 0 or more a characters

A + matches any sequence of one or more a characters

A? Match 0 or 1 a character

De | abc matching sequence de or abc

(abc) * match 0 or more instances of sequence adc

{n}, {mdirection n} {n}, or {mdirection n} symbols provide a more general way to write regular expressions that match many of the aforementioned atoms (or "parts") of the pattern. 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 {0jue 1}

[a-dX] matches any character that is a _ between two other characters to form a range.

[^ a-dX] matches any character that is not a ~ b ~. The preceding character'^ 'means negative.

[.characters.] In a parenthetical expression (using [and]), match the sequence of characters used to proofread elements, and the characters are character names such as a single character or a new line

Mysql > SELECT'~ 'REGEXP' [[. ~.]';-> 1

Mysql > SELECT'~ 'REGEXP' [[.tilde]]';-> 1

[= character_class=]

In parenthetical expressions (using [and]), [= character_class=] denotes the like. It matches all characters with the same proofreading value, including itself

[[= a =]] is equivalent to [a (+)], [a +], [a {1,}]

[: character_class:]

In parenthetical expressions (using [and]), [: character_class:] represents a character class that matches all characters of the term class.

The standard class name is:

Alnum literal numeric character

Alpha literal character

Blank white space character

Cntrl control character

Digit numeric character

Graph graphic character

Lower lowercase text characters

Print graphics or space characters

Punct punctuation character

Space spaces, tabs, new lines, and carriage returns

Upper uppercase text character

Xdigit hexadecimal digit character

[[::]]

These tags represent word boundaries. They match the start and end of the word, respectively. Word is a series of word characters that are not preceded by or followed by word characters. Characters are alphanumeric characters or underscores (_) in the alnum class.

Mysql > select 'fang shan zi' regexp' [[::]]';-> 1

Mysql > select 'fang shan zi' regexp' [[::]]';-> 1

Mysql > select 'fang shans zi' regexp' [[::]]';-> 0

Regular expressions use special characters, preceded by 2 backslash''characters

Mysql > SELECT'1x 2' REGEXP'1x 2a;-> 0

Mysql > SELECT'1x 2' REGEXP'1x 2a;-> 0

Mysql > SELECT'1, 2 'REGEXP' 1\ + 2;-> 1

MySQL lookup conditions use regular regexp

I use Mybatis.

T.hobby: conditional field

Hobby: lookup parameters. Values can be values separated by commas: 'read, make friends, go'

Concat (',', REPLACE (t.hobby,','),',') regexp concat (', (, replace (# {hobby},'|),'))

Concat (',', REPLACE ('eat, drink, whore, gamble, smoke, pit, trick, kidnap, cheat, steal,',',) regexp concat (, (, replace (# {hobby},'|),'))

Concat (', 'eating, drinking, whoring, gambling, smoking, deceiving, cheating, stealing,) regexp concat (, (, replace (# {hobby},' |),'))

', eat, drink, go whoring, gamble, smoke, trap, trick, kidnap, cheat, steal,' regexp concat (, (), replace (# {hobby},'|),'))

, eating, drinking, whoring, gambling, smoking, cheating, cheating, stealing, 'regexp concat (', (', replace ('eating, drinking, whoring, whoring,' |),'))

', eat, drink, go whoring, gamble, smoke, cheat, cheat, steal,' regexp concat (', (', 'eat | drink | whoring | whoring,'))

', eating, drinking, whoring, gambling, smoking, cheating, cheating, stealing,' regexp', (eating | drinking | whoring | whoring),'

It is true that the result is 1 as a condition.

Complex processes are mainly used to deal with search conditions. It is ok to get regular operating conditions that meet the requirements.

But personally, I think a better way is:

The searched field was formatted when it was first stored in the data: 'eat, drink, go whoring, gamble, smoke, trap, trick, cheat, steal'

Search conditions can be handled and passed in:', (eat | drink | whoring | whoring),'

Then a search can be reduced to this.

Concat (',', t.hobby,',') regexp # {hobby}

The above is how to use Regexp regular expressions in Mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.