Mysql fuzzy query like and REGEXP Usage Details, fuzzy query regexp

Mysql fuzzy query like and REGEXP Usage Details, fuzzy query regexp

Preface

In mysql, like and regexp are implemented for Fuzzy queries. This article describes how to use these two methods in detail through the instance code. Let's learn from them in the following section.

Like Mode

Like indicates that it looks like and has two modes: _ and %.

_ Indicates a single character, which is usually used to query long data. For example, if you find the names of all the three characters in the surname Wang, assume that the name column is name. Note that "Wang" is followed by two characters _

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

% Indicates 0 or multiple arbitrary characters. For example, you can find the names of all the kings.

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

Find out the names of all people who contain the word "Hua"

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

Regular Expression

^: Match the start position of the string. In the preceding example, query the names of all the kings.

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

$: Matches the end position of the string. For example, you can query the names of all names whose names are "Ming" at the end.

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

., Match any single character except \ n, similar to _. No SQL statement is written.

[……], Match any character in [], abcdef ...... Xyz can be abbreviated as [a-z], and 0123456789 is abbreviated as [0-9]. For example, you can query the names of people starting with w/z/s.

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

[^…], Match characters not included in [], for example, the name of a person except the start of w/z/s

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

A | B | c, matching a, B, or c. For example, if an employee with A-, A, or A + performance is identified, assume that the performance column name is "performance ".

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

* Repeated 0 or multiple times. Anyone familiar with javascript Regular Expressions knows that

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

?, Repeated 0 or 1 times

'Str? 'Can match st/str

+, Repeat once or multiple times

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

Compared with the regular expressions in javascript, the regular expressions here are simplified, and there is no inert/greedy match. The \ w \ s \ d syntax is not supported in [], and Chinese characters are not supported, relatively simple.

Note that:Do not mix the two modes. The like mode does not support regular expressions, and the REGEXP mode does not know _ and %.

Summary

The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.