Tutorial on using regular expressions to search and match in mysql (2)

Or matching

To search for one of two strings (either this string or another string), use |, as shown below:

Input:

select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

Output:

Analysis: The regular expression 1000|2000 is used in the statement. | is the or operator of regular expressions. It says match one of them, so both 1000 and 2000 are matched and returned.

Using | is functionally similar to using the or statement in a select statement. Multiple or conditions can be merged into a single regular expression.

Note: More than two or conditions can be given. For example '1000|2000|3000' will match 1000 or 2000 or 3000.

matches one of several characters

matches any single character. But what if you only want to match specific characters?

This can be done by formulating a set of characters enclosed by [and], as shown below:

Input: select prod_name from products where prod_name regexp '[123] Ton' order by prod_name ;

Output:

Analysis: Here, the regular expression [123]Ton is used. [123] defines a set of characters that means matching 1 or 2 or 3. Therefore, both 1ton and 2ton are matched and returned (no 3ton).

As you can see, [] is another form of or statement. In fact, the regular expression [123]Ton is the abbreviation of [1|2|3]Ton, and the latter can also be used. However, [] is needed to define what the or statement is looking for. To better understand this, one should look at the following example:

Input:

select prod_name from products where prod_name regexp '1|2|3 Ton‘ order by prod_name;

Output:

Analysis: This does not Not the expected output. The two requested rows were retrieved, but three additional rows were retrieved, and the reason for this is because MySQL assumes you meant '1' or '2' or '3ton'. Unless the character | is expanded into a set, it applies to the entire string.

Character sets can also be negated, i.e. they will match anything except the specified characters. To negate a character set, place a ^ at the beginning of the set. So while [123] matches characters 1, 2, or 3, [^123] matches anything except those characters.

Match range

The collection can be used to define one or more characters to match. For example, a collection of lines and polygons would match the numbers 0 to 9:

[123456789]

To simplify this type of collection, you can use - to define a range. The following formula is functionally equivalent to the above list of numbers:

[0-9]

The range is not limited to the complete set, [1-3] and [6-9] are also legal scope. Additionally, the range does not have to be numeric only, [a-z] matches any alphabetic character.

For example:

Input:

select prod_name from products where prod_name regexp '[1-5] Ton’ order by prod_name;

Output:

Analysis: Use regular expressions here [ 1-5]Ton. [1-5] defines a range. This expression means to match 1 to 5, so 3 matching rows are returned. Since 5ton matches, .5 ton is returned.

【Related Recommendations】

1. Tutorial on using regular expressions to search and match in mysql (1)

2. MySQL Free Online video tutorial

3. Han Shunping’s latest MySQL basic video tutorial in 2016

4.mSearch and match tutorial using regular expressions in ysql (three)

The above is the detailed content of Tutorial on using regular expressions to search and match in mysql (2). For more information, please follow other related articles on the PHP Chinese website!