How to query using regular expressions in mysql

Basic Form

Property name regexp 'matching Method'

Regular Expression Pattern character

^ Starting from matching characters

Eg1: query records starting with L from the name field of the info table

Select * from info where name regexp '^ l ';

Eg2: query records starting with aaa from the name field of the info table

Select * from info where name regexp '^ aaa ';

$ End part of matching character

Eg1: query records ending with c from the name field of the info table

Select * from info where name regexp 'C $ ';

Eg2: query records ending with aaa from the name field of the info table

Select * from info where name regexp 'aaa $ ';

. Match any character in the string, including carriage return and line feed

Eg1: query records starting with L and ending with y with two arbitrary characters from the name field of the info table.

Select * from info where name regexp '^ L .. y $ ';

[Character Set combination] match any character in the character set

Eg1: Query any of the three letters c, e, and o in the name field of the info table.

Select * from info where name regexp '[ceo]';

Eg2: query records containing numbers from the name field of the info table

Select * from info where name regexp '[0-9]';

Eg3: query records that contain numbers or any one of the letters a, B, and c from the name field of the info table.

Select * from info where name regexp '[0-9a-c]';

[^ Character Set combination] match any character except Character Set combination

Eg1: query records that contain a-w letters and other characters from the name field of the info table.

Select * from info where name regexp '[^ a-w0-9]';

S1 | s2 | s3 matches any one of s1s2s3

Eg1: query records containing 'ic' from the name field of the info table.

Select * from info where name regexp 'ic ';

Eg2: Query any of the three strings ic, uc, and AB from the name field of the info table.

Select * from info where name regexp 'ic | uc | AB ';

* Represents multiple characters before the character, including 0 or 1

Eg1: query records of a before c in the name field of info table

Select * from info where name regexp 'a * C ';

+ Represents multiple characters before this character, including one

Eg1: query records of a before c in the name field of info table

Select * from info where name regexp 'a + C'; (Note the comparison result !)

String {N} appears N times

Eg1: Query a3 records from the name field of the info table.

Select * from info where name regexp 'a {3 }';

The string {M, N} appears at least M times and at most N times

Eg1: query the name field of the info table for records that appear at least once and at most three times.

Select * from info where name regexp 'AB {1, 3 }';

MYSQL self-contained wildcard (LIKE keyword)

% Can represent any length of characters (including 0)

-Represents a single character.