MySQL Regular Expression search, mysql Regular Expression

MySQL Regular Expression search, mysql Regular Expression

The products table is as follows:

1. Basic Character matching

The difference between regular expression and LIKE is that regular expression searches for the entire column, As long as prod_name contains the searched characters, and if LIKE does not use wildcards, therefore, it is required that prod_name be exactly matched with the search characters: that is to say, in the following example, you need to use LIKE 'jetpack 1000 'to adapt to the search.

2. Use or for matching search. You can search for two conditions or connect multiple conditions:

3 matches one of the following characters: // This means that it matches 1 Ton, 2 Ton, or 3 Ton.

However, if it is written as follows, it is not correct: // This means that the column matches 1, 2, or 3 Ton.

4. Range matching. [1-5] is actually short for [12345]. In addition, [0-9] and [3-9] are valid.

5. Special characters must be matched. \ is required to match special characters \\

6. Match multiple instances

Repeated element characters:

Metacharacters	Description
*	0 or multiple matches
+	One or more matches
?	0 or 1 matching
{N}	Match of the specified number
{N ,}	Match of no less than a specified number
{N, m}	Matching quantity range

The following search condition indicates that there must be a right brace first, followed by a number, followed by a space, followed by sticks, followed by 0 or 1 left brace, the meanings of question marks are shown in the table above.

For ease of work, you can use pre-defined character sets, which are as follows:

Class	Description
[: Alpha:]	Any character
[: Blank:]	Spaces and tabs
[: Cntrl:]	ASCII control characters (from ASCII0 to 31, 127)
[: Digit:]	Any number is the same as [0-9]
[: Graph:]	Any printable character, excluding spaces
[: Lower:]	Any lowercase letter
[: Print:]	Any printable character
[: Punct:]	That is, any character not in [: alnum:] and not in [: cntrl :]
[: Space:]	Any blank characters including Spaces
[: Upper:]	Any uppercase letter
[: Alnum:]	Any letter or number

Next, we will search the table for the names of four consecutive numbers in prod_name: [: digit:] indicates any number, and {4} indicates that this number needs to appear four times:

8. Search at a specific location

Metacharacters	Description
^	Start text
$	End of Text
[[: <:]	Start of Word
[[:>:]	End of a word

In the following statement, find the prod_name starting with a number or.

MySql Regular Expression

Here are some materials for you to see
Regular Expression:
Regular Expressions are a powerful way to specify patterns for complex searches.
^
Start of the string following the matched string
Mysql> select "fonfo" REGEXP "^ fo $";-> 0 (mismatch)
Mysql> select "fofo" REGEXP "^ fo";-> 1 (matching)
$
The end of the matched string
Mysql> select "fono" REGEXP "^ fono $";-> 1 (matching)
Mysql> select "fono" REGEXP "^ fo $";-> 0 (mismatch)
.
Match any characters (including new lines)
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 one or more a characters. Mysql> select "Ban" REGEXP "^ Ba + n";-> 1 (matching)
Mysql> select "Bn" REGEXP "^ Ba + n";-> 0 (indicating unmatched)?
Match one or zero
Mysql> select "Bn" REGEXP "^ Ba? N ";-> 1 (indicating matching)
Mysql> select "Ban" REGEXP "^ Ba? N ";-> 1 (indicating matching)
Mysql> select "Baan" REGEXP "^ Ba? N ";-> 0 (indicating unmatched) de | abc
Match de or abc
Mysql> select "pi" REGEXP "pi | apa";-> 1 (indicating matching)
Mysql> select "axe" REGEXP "pi | apa";-> 0 (mismatch)
Mysql> select "apa" REGEXP "pi | apa";-> 1 (indicating matching)
Mysql> select "apa" REGEXP "^ (pi | apa) $ ";
 
A mysql Regular Expression

Select * from tableA where style like "111% ";
You can write it like this because it starts with 111.
This is the case for regular expressions.
SELECT * FROM tableA WHERE style REGEXP '^ 111 .*';