MySQL Fuzzy query like/regexp

Adding and Deleting is the most basic function of MySQL, which is the most frequent operation, Fuzzy Lookup is very common in the query operation, so fuzzy search has become a compulsory course.

Like mode

Like means look, there are two modes: _ and%

_ denotes a single character, usually used to query a fixed length of data, such as the names of all the three characters of the king, assuming the name column named name, note that "Wang" followed by two _

Select name from table name where name like ' King __ ';

% represents 0 or more arbitrary characters, such as the name of all Wang

Select name from table name where name like ' King% ';

Find out all the names that contain the word "Hua"

Select name from table name where name like '% Hua ';

Regular Mode

^, match the starting position of the string, or the above example, query all the names of the king

Select name from table name where name regexp ' ^ King ';

$, matches the end of the string, such as querying all names at the end of the name "Ming"

Select name from table name where name RegExp ' Ming $ ';

., match any single character except \ n, like _, without writing the SQL statement

[...], matching any one of the characters contained in [], ABCDEF......XYZ can be abbreviated as [a-z],0123456789] [0-9], such as the name of the person who w/z/s the beginning of the query.

Select name from table name where name RegExp ' ^[wzs] ';

[^ ...], matching characters not included in [], such as querying for names other than the beginning of w/z/s

Select name from table name where name RegExp ' ^[^wzs] ';

A|b|c, match A or B or C, if the employee who performance is a-or a or a + is identified, assuming the performance column name performance

Select performance from table name where performance RegExp ' a-| a| A + ';

*, repeat 0 or more times, familiar with JavaScript regular students know

' str* ' can match st/str/strr/strrr ...

?, repeat 0 or 1 times

' Str? ' Can match St/str

+, repeat 1 or more times

' str+ ' can match str/strr/strrr/strrrr ...

Compared to the regular in JavaScript, here the regular is a simplified version, there is no lazy match/greedy match, [] \w\s\d this syntax is not supported, also does not support Chinese, relatively simple.

one point to note is that the two modes do not mix, like mode is not supported regular expression, RegExp mode does not know _ and%

MySQL Fuzzy query like/regexp