"MySQL must learn Notes": Regular expressions

The application of regular expressions

The previous sections of the filter example allow matching, comparison, and wildcard operators to find data. For basic data filtering, this is enough. However, as the complexity of the filtering conditions increases, the complexity of the WHERE clause itself is also necessary to increase. This is where the regular expression becomes useful.

A regular expression is used to match a particular character set in the text.

The regular expression is not the first time you see, in java/c++ these languages, there are regular expressions, but their purpose is the same, are matching the text string. Perhaps the specific usage is not the same, but it is similar. The following will be studied and introduced.

Matching of basic characters

When we want to find out if there is a character in a column, it can be done by using the wildcard character described in the previous blog post, but it can also be done with regular expressions. As follows:

select * from student where name regexp ‘1‘ order by id;//找出name中含有 字符1的记录。

From the above example, it may not be possible to see the benefits of regular expressions, and this is not the same as a wildcard. It is normal to have this feeling, as this example does not reflect the power of the regular expression. After reading the following introduction you may slowly feel the power of the regular expression.

Make or Match

Used in regular expressions when searching for multiple strings | be implemented.

select * from student where name regexp ‘123|456‘;//|为正则表达式的or操作符。它表示匹配其中之一，因此，只要是字符串中含有123或者是456的记录就返回。

The practice results are as follows:

Match one of several characters

What if you just want to match a specific character?

• The solution is to do this by setting up a set of characters that are enclosed in [and].

For example, if you want to match all of the data in a string with even numbers, you just need to put the specified data into it using [], as follows:

select * from student where name regex ‘[02468]‘ order by score;//通过[02468]就可以匹配了

[] is a statement of another form of the OR operator. In fact, [02468] is equivalent to 0|2|4|6|8

Match Range

The above set [02468] is used to match multiple characters, while [0123456789] This set is the matching number 0 to 9;
To simplify this type of collection, you can use - to define a range.
For example: [0123456789] can be written as [0-9];

The range is valid as long as it is contiguous, such as [1-3] [7-9] and [b-g] these sets are OK.

Match Special characters

A regular expression consists of special characters with specific meanings, such as [] |. Wait a minute.
When we want to match these characters in a string, how do we match them?

believe that learning Java or C + + know that if you want to match these special characters, it should be escaped, escaped the method is: in front of these special characters with two backslash, that is \ \; For example, \\[, \ \] \\| \\. such as

The following is an example of this particular symbol in a matching string .

select * from student where name regexp ‘\\.‘;//返回的结果就是含有字符 . 的结果

If we don't use \ \ To escape the point, then because the point. is used to match any one character, so all results will appear, not what we want.

The specific results are as follows:

Match character class

Match the character class, it can be understood that, for convenience, the system provides us with some shorthand. For example, use \\d in Java to match any number, and in MySQL use [:d igit:] to match any number .

See the table below (the image is from MySQL must know))

Match multiple instances

The regular expressions used so far have tried to match a single occurrence. That is, if there is a match, the row is retrieved, otherwise it is not retrieved.

However, there are times when you need to have more control over the number of matches. For example, when you look for a word, you also want it to find the plural form of the word (that is, the trailing s).

select * from student where name regexp ‘rang?‘;//匹配name中含有ran或者是带有后缀g的数据。

More symbols for matching multiple instances are as follows:

Locator characters

All of the previous descriptions are matches of any location, but sometimes we want to match at a particular location, for example, if you want to match the beginning of a string with a point, rather than whether there is a point in the matching string anywhere. This requires a locator.

Examples of use are shown below:

select * from student where name  regexp ‘^\\.‘;//检查字符串是否以点开始。

Note: ^ There are two ways to use this:

• In the collection (defined with [and]), it is used to negate the collection, for example [^01234]: refers to the match except for 012345 other characters.
• Used to refer to the beginning of a string.

Summary

The above introduction of things more, I believe suddenly also more difficult to master good, but, remind everyone is, do not force themselves to remember these details, when we more than a few times the regular expression, these grammatical aspects of the details mastered, even if not mastered, when we need, check the information on OK. Anyway, as far as I am concerned, whether it is learning Java or other language about the regular expression of this piece of detail, I have not to remember, when necessary, I will check the information and then go to write just fine.

"MySQL must learn Notes": Regular expressions